统计oracle 数据库 lawpeople表lawtype字段多个值只统计一次有关问题，按照地区分类

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