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 .....方式过滤一遍,这样数据就不会多于了,在代码中直接查询这个视图就可以了。