统计oracle 数据库 lawpeople表lawtype字段多个值只统计一次问题，按照地区分类
select temparea.name,(case when lawtype like '%501%' then 501 when lawtype like '%502%' then 502 when lawtype like '%503%' then 503 when lawtype like '%504%' then 504 when lawtype like '%505%'?then 505 when lawtype like '%506%' then 506 when lawtype like '%507%' then 507 when lawtype like '%508%' then 508 when lawtype like '%509%' then 509 when lawtype like '%510%'?then 510 else null end)lawtype, temparea.supid,temparea.id from lawpeople law inner join area temparea on law.areaid= temparea.id where (ADDNEW is null or ADDNEW=4 or ADDNEW=6 ) and ( RETIREMENT is null or RETIREMENT=0 or RETIREMENT=1 or RETIREMENT=2 or RETIREMENT=3 or?RETIREMENT=5 ) and (TRANSFERRED is null or TRANSFERRED=0 or TRANSFERRED=1 or TRANSFERRED=2 or TRANSFERRED=3 or TRANSFERRED=5 ) and (OTHERREASONS is null or OTHERREASONS=0 or OTHERREASONS=1 or OTHERREASONS=2 or OTHERREASONS=3 or OTHERREASONS=5 ) and (BATCHLAWCODE is null or BATCHLAWCODE= 4 or BATCHLAWCODE= 6 );
?
上面的是视图。
这是统计的sql:
select rownum n,temp.* from ( select name,sum(case when lawtype like '%501%' THEN 1 else 0 end)?zongHeZhiFa, sum(case when lawtype like '%502%' THEN 1 else 0 end) binWeiWuZhong, sum(case when lawtype like '%503%' THEN 1 else 0 end) shuMuZhongMiao,?sum(case when lawtype like '%504%' THEN 1 else 0 end) linZhengGuanLi, sum(case when lawtype like '%505%' THEN 1 else 0 end) senLinGongAn, sum(case when lawtype like '%506%' THEN 1 else 0 end) shengTaiGongCheng, sum(case when lawtype like '%507%' THEN 1 else 0 end) yeShengDongWuBaoHu, sum(case when lawtype like '%508%' THEN 1 else 0 end) zhiWuJianYi, sum(case when lawtype like '%509%' THEN 1 else 0 end) zhiWuXinPinZhong, sum(case when lawtype like '%510%' THEN 1 else 0 end) other, supid,id from arealawtype law group by name,id ,supid HAVING id=728 or supid= 728 order by id asc ) temp?
以下是测试数据:
?
?
数据库中 lawtype存的是
序号 姓名?? 类别
1??? 姓名?? 501,502,503
?
如果按照
?sum(case when lawtype like '%504%' THEN 1 else 0 end) linZhengGuanLi,
这钟方式直接统计，那么统计出来的数据比原有数据要多，因为501的统计了一次，502的又把这条数据统计了一次，所以统计出来的数据要多。
?
怎样才能不多呢？
?
我建了个视图，把需要的类别用case when? then? when then .....方式过滤一遍，这样数据就不会多于了，在代码中直接查询这个视图就可以了。
?
?
?
