试试:
select '全国' as gsmc ,
sum(deocde(sfcl,0,1,1,0)) as 查获,
sum(sfcl) as 查处,
sum(decode(sfcl,0,1,1,0) * decode(sfda,0,1,1,0)) as 大案查获,
sum(sfcl * decode(sfda,0,1,1,0)) as 大案查处
from ajdj
union
select a.gsmc
sum(deocde(b.sfcl,0,1,1,0)) as 查获,
sum(b.sfcl) as 查处,
sum(decode(b.sfcl,0,1,1,0) * decode(b.sfda,0,1,1,0)) as 大案查获,
sum(b.sfcl * decode(b.sfda,0,1,1,0)) as 大案查处
from
gsxx a
inner join
ajdj b
on
a.gsid = b.gsid
group by
a.gsmc
select '全国' as gsmc ,
sum(deocde(sfcl,0,1,1,0)) as 查获,
sum(sfcl) as 查处,
sum(decode(sfcl,0,1,1,0) * decode(sfda,0,1,1,0)) as 大案查获,
sum(sfcl * decode(sfda,0,1,1,0)) as 大案查处
from ajdj
union
select a.gsmc
sum(deocde(b.sfcl,0,1,1,0)) as 查获,
sum(b.sfcl) as 查处,
sum(decode(b.sfcl,0,1,1,0) * decode(b.sfda,0,1,1,0)) as 大案查获,
sum(b.sfcl * decode(b.sfda,0,1,1,0)) as 大案查处
from
gsxx a
inner join
ajdj b
on
a.gsid = b.gsid
group by
a.gsmc
SQL> Insert Into ajdj Values('1','aj1','0','0');1 行 已插入SQL> Insert Into ajdj Values('1','aj2','1','0');1 行 已插入SQL> Insert Into ajdj Values('1','aj3','1','1');1 行 已插入SQL> Insert Into ajdj Values('2','aj4','1','1');1 行 已插入SQL> Insert Into ajdj Values('3','aj5','0','0');1 行 已插入SQL> Insert Into ajdj Values('3','aj6','1','0');1 行 已插入SQL> Insert Into ajdj Values('4','aj7','0','1');1 行 已插入SQL>
SQL> Insert Into gsxx Values('1','北京');1 行 已插入SQL> Insert Into gsxx Values('2','上海');1 行 已插入SQL> Insert Into gsxx Values('3','天津');1 行 已插入SQL> Insert Into gsxx Values('4','南京');1 行 已插入SQL> Insert Into gsxx Values('5','重庆');1 行 已插入SQL>
SQL> Select Nvl(Gsmc, '全国'), Ch, Cc, Dach, Dacc
2 From (Select b.Gsmc Gsmc,
3 Sum(Decode(Sfcl, '0', 1, 0)) As Ch,
4 Sum(Decode(Sfcl, '1', 1, 0)) As Cc,
5 Sum(Decode(Sfcl, '0', 1, 0, Decode(Sfda, '0', 1, 0))) As Dach,
6 Sum(Decode(Sfcl, '1', 1, 0, Decode(Sfda, '1', 1, 0))) As Dacc
7 From Ajdj a, Gsxx b
8 Where a.Gsid = b.Gsid
9 Group By Rollup(b.Gsmc))
10 Order By Ch Desc
11 ;NVL(GSMC,'全国') CH CC DACH DACC
---------------- ---------- ---------- ---------- ----------
全国 3 4 3 5
北京 1 2 1 2
南京 1 0 1 1
天津 1 1 1 1
上海 0 1 1SQL>
sum(decode(sfcl+sfda),0,1) 大案查获,,sum(decode(sfcl+sfda),1,1) 大案查处 from ajdj
union
select gsxx.gsmc,sum(decode(sfcl),0,1) 查获,sum(decode(sfcl),1,1) 查处,
sum(decode(sfcl+sfda),0,1) 大案查获,,sum(decode(sfcl+sfda),1,1) 大案查处 from ajdj,gsxx where ajdj.gsid=gsxx.gsid
group by gsxx.gsid;
from
(select gsid,count(ajmc) as 查获 from ajdj where sfc1 = 0 group by gsid ) A,
(select gsid,count(ajmc) as 查处 from ajdj where sfc1 = 1 group by gsid ) B,
(select gsid,count(ajmc) as 大案查获 from ajdj where sfc1 = 0 and sfda = 0 group by gsid ) C,
(select gsid,count(ajmc) as 大案查处 from ajdj where sfc1 = 1 and sfda = 0 group by gsid ) D,gsxx E
where A.gsid = E.gsid AND A.gsid = B.gsid and A.gsid = C.gsid and A.gsid =D.gsid