create table Z
(
RIQI VARCHAR2(10),
APN VARCHAR2(60),
ASA VARCHAR2(60),
SHICHANG NUMBER,
ACC_RESULT NUMBER(20,2),
FEE_NUMBER NUMBER(8) not null
);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-11', '16000171', '56723986', 2, 200, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-12', '16000172', '68126337', 3, 300, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000173', '25881225', 2, 200, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000174', '64164232', 2, 200, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-14', '16000175', '69177818', 8, 800, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-14', '16000176', '63304666', 3, 300, 200);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000177', '62092489', 5, 500, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000178', '65188457', 3, 300, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '64168782', 1, 100, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '53027205', 1, 100, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-15', '16000176', '64212807', 3, 300, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-13', '16000176', '64364641', 2, 200, 300);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-16', '16000179', '32313222', 1, 100, 100);
insert into Z (RIQI, APN, ASA, SHICHANG, ACC_RESULT, FEE_NUMBER)
values ('2009-10-17', '16000179', '53524520', 3, 300, 100);
commit;
测试数据如上所示
我想得到如下数据，建附件图片
我自己的sql如下：
select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num
from z
group by z.fee_number ,z.riqi,z.apn
order by z.fee_number,z.riqi,z.apn
但是却不能按照fee_number再进行统计啦，各位有什么高招吗？做个样列sql吧，谢谢了！
------解决方案--------------------
啥意思》？这样吗？
select z.riqi,z.apn,z.fee_number,sum(z.fee_number) over (partition by z.fee_number) a,sum(z.acc_result),count(*) num
from z
group by z.fee_number ,z.riqi,z.apn
order by z.fee_number,z.riqi,z.apn
------解决方案--------------------
- SQL code
select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num from z group by Rollup (z.fee_number ,z.riqi,z.apn) order by z.fee_number,z.riqi,z.apn
------解决方案--------------------
- SQL code
select * from ( select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num from z group by rollup(z.fee_number ,z.riqi,z.apn) order by z.fee_number,z.riqi,z.apn )where (riqi is null and apn is null) or (riqi is not null and apn is not null)
------解决方案--------------------
- SQL code
RIQI APN FEE_NUMBER SUM(Z.ACC_RESULT) NUM2009-10-11 16000171 100 200 12009-10-12 16000172 100 300 12009-10-13 16000177 100 500 12009-10-13 16000178 100 300 12009-10-16 16000179 100 100 12009-10-17 16000179 100 300 1 100 1700 62009-10-13 16000173 200 200 12009-10-13 16000174 200 200 12009-10-14 16000175 200 800 12009-10-14 16000176 200 300 1 200 1500 42009-10-13 16000176 300 200 12009-10-15 16000176 300 500 3 300 700 4 3900 14
------解决方案--------------------
- SQL code
Select z.Riqi, z.Apn, z.Fee_Number, Sum(z.Acc_Result), Count(*) Num From z Group By Rollup(z.Fee_Number, (z.Riqi, z.Apn, z.Fee_Number)) Order By z.Fee_Number, z.Riqi, z.Apn
------解决方案--------------------
- SQL code
SQL> select z.riqi,z.apn,z.fee_number,sum(z.acc_result),count(*) num 2 from z 3 group by grouping sets(rollup(z.fee_number,(z.riqi,z.apn))) 4 order by z.fee_number,z.riqi,z.apn ; RIQI APN FEE_NUMBER SUM(Z.ACC_RESULT) NUM---------- ------------------------ ---------- ----------------- ----------2009-10-11 16000171 100 200 12009-10-12 16000172 100 300 12009-10-13 16000177 100 500 12009-10-13 16000178 100 300 12009-10-16 16000179 100 100 12009-10-17 16000179 100 300 1 100 1700 62009-10-13 16000173 200 200 12009-10-13 16000174 200 200 12009-10-14 16000175 200 800 12009-10-14 16000176 200 300 1 200 1500 42009-10-13 16000176 300 200 12009-10-15 16000176 300 500 3 300 700 4 3900 14 16 rows selected
------解决方案--------------------
- SQL code
SQL> select z.riqi,z.apn,case when z.riqi is null and z.apn is null and z.fee_number is not null then '小计' 2 when z.riqi is null and z.apn is null and z.fee_number is null then '合计' 3 else to_char(z.fee_number) end fee_number ,sum(z.acc_result),count(*) num 4 from z 5 group by grouping sets(rollup(z.fee_number,(z.riqi,z.apn))) 6 order by z.fee_number,z.riqi,z.apn ; RIQI APN FEE_NUMBER SUM(Z.ACC_RESULT) NUM---------- ------------------------ ---------------------------------------- ----------------- ----------2009-10-11 16000171 100 200 12009-10-12 16000172 100 300 12009-10-13 16000177 100 500 12009-10-13 16000178 100 300 12009-10-16 16000179 100 100 12009-10-17 16000179 100 300 1 小计 1700 62009-10-13 16000173 200 200 12009-10-13 16000174 200 200 12009-10-14 16000175 200 800 12009-10-14 16000176 200 300 1 小计 1500 42009-10-13 16000176 300 200 12009-10-15 16000176 300 500 3 小计 700 4 合计 3900 14 16 rows selected
------解决方案--------------------
真难。。想学习一下啦。。。帮你顶贴。。。。lz要给点辛苦分哦。。。。
------解决方案--------------------
太长了 没时间看啊.
帮你顶下.
------解决方案--------------------
- SQL code
SELECT DECODE(GROUPING(riqi)+GROUPING(fee_number),'1','小记','2','合计',z.riqi) riqi, z.apn, DECODE(GROUPING(riqi),0,z.fee_number) fee_number, SUM(z.acc_result), COUNT(*) numFROM zGROUP BY grouping sets((z.fee_number ,z.riqi,z.apn),(z.fee_number),1)ORDER BY z.fee_number, z.riqi, z.apn
------解决方案--------------------
- SQL code
--互相学习相互提升SQL> edi已写入 file afiedt.buf 1 select case when grouping(riqi)=1 and grouping(apn)=1 and grouping(FEE_NUMBER)=1 2 then '总计' else riqi end riqi, 3 case when grouping(apn)=1 and grouping(riqi)=1 and grouping(FEE_NUMBER)=0 4 then '小计' else apn end apn, 5 FEE_NUMBER, 6 sum(ACC_RESULT) jine,count(*) num 7 from z 8 group by rollup(FEE_NUMBER,riqi,apn) 9* having grouping(apn)=0 or grouping(riqi)=1SQL> /RIQI APN FEE_NUMBER JINE NUM---------- -------------------- ---------- ---------- ----------2009-10-11 16000171 100 200 12009-10-12 16000172 100 300 12009-10-13 16000177 100 500 12009-10-13 16000178 100 300 12009-10-16 16000179 100 100 12009-10-17 16000179 100 300 1 小计 100 1700 62009-10-13 16000173 200 200 12009-10-13 16000174 200 200 12009-10-14 16000175 200 800 12009-10-14 16000176 200 300 1 小计 200 1500 42009-10-13 16000176 300 200 12009-10-15 16000176 300 500 3 小计 300 700 4总计 3900 14已选择16行。
------解决方案--------------------
- SQL code
SELECT decode(grouping_id(z.riqi,z.fee_number),0,z.riqi,2,'小计',3,'总计') riqi, z.apn, z.fee_number, SUM(z.acc_result), COUNT(*) num FROM z GROUP BY rollup(z.fee_number,(z.riqi, z.apn)) ORDER BY z.fee_number, z.riqi, z.apn