select a.district,sum(decode(sign(a.type-3),-1,a.type,0) as "type<=3",
sum(decode(sign(a.type-3),-1,0,a.type) as "type>3"
from actioninfo a,kfinfo b
where a.kfno = b.kfno(+)
group by a.district
sum(decode(sign(a.type-3),-1,0,a.type) as "type>3"
from actioninfo a,kfinfo b
where a.kfno = b.kfno(+)
group by a.district
sum(decode(sign(a.type-3),-1,0,a.type) type>3
from actioninfo a,kfinfo b
where a.kfno = b.kfno(+)
group by a.district
select a.district,sum(case when a.type <= 3 then 1 else 0 end) as "type<=3",
sum(case when a.type > 3 then 1 else 0 end) as "type>3"
from actioninfo a,kfinfo b
where a.kfno = b.kfno(+)
group by a.district
改为:
select b.district,sum(case when a.type <= 3 then 1 else 0 end) as type<=3,
sum(case when a.type > 3 then 1 else 0 end) as type>3
from actioninfo a,kfinfo b
where b.kfno = a.kfno(+)
group by b.district
sum(case when a.type > 3 then 1 else 0 end) as "type>3"
from actioninfo a,kfinfo b
where b.kfno = a.kfno
group by b.district
这个语句已经过试验!
select b.district,sum(case when a.type <= 3 then 1 else 0 end) as "type<=3",
sum(case when a.type > 3 then 1 else 0 end) as "type>3"
from actioninfo a,kfinfo b
where b.kfno = a.kfno(+)
group by b.district
select district,count(*) a,0 b from (select district,type from kfinfo left join actioninfo on kfinfo.knno=actioninfo.kfno) where type<=3 group by district
union all
select district,0 a,count(*) b from (select district,type from kfinfo left join actioninfo on kfinfo.knno=actioninfo.kfno) where type> 3 group by district
) group by district;