3个字段group by，然后再统计的sql语句的有关问题
3个字段group by，然后再统计的sql语句的问题！
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
------解决方案--------------------
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) NUM
2009-10-11 16000171 100 200 1
2009-10-12 16000172 100 300 1
2009-10-13 16000177 100 500 1
2009-10-13 16000178 100 300 1
2009-10-16 16000179 100 100 1
2009-10-17 16000179 100 300 1
100 1700 6
2009-10-13 16000173 200 200 1
2009-10-13 16000174 200 200 1
2009-10-14 16000175 200 800 1
2009-10-14 16000176 200 300 1
200 1500 4
2009-10-13 16000176 300 200 1
2009-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 1
2009-10-12 16000172 100 300 1
2009-10-13 16000177 100 500 1
2009-10-13 16000178 100 300 1
2009-10-16 16000179 100 100 1
2009-10-17 16000179 100 300 1
100 1700 6
2009-10-13 16000173 200 200 1
2009-10-13 16000174 200 200 1
2009-10-14 16000175 200 800 1
2009-10-14 16000176 200 300 1
200 1500 4
2009-10-13 16000176 300 200 1
2009-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 1
2009-10-12 16000172 100 300 1
2009-10-13 16000177 100 500 1
2009-10-13 16000178 100 300 1
2009-10-16 16000179 100 100 1
2009-10-17 16000179 100 300 1
小计 1700 6
2009-10-13 16000173 200 200 1
2009-10-13 16000174 200 200 1
2009-10-14 16000175 200 800 1
2009-10-14 16000176 200 300 1
小计 1500 4
2009-10-13 16000176 300 200 1
2009-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(*) num FROM z GROUP 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)=1
SQL> /
RIQI APN FEE_NUMBER JINE NUM
---------- -------------------- ---------- ---------- ----------
2009-10-11 16000171 100 200 1
2009-10-12 16000172 100 300 1
2009-10-13 16000177 100 500 1
2009-10-13 16000178 100 300 1
2009-10-16 16000179 100 100 1
2009-10-17 16000179 100 300 1
小计 100 1700 6
2009-10-13 16000173 200 200 1
2009-10-13 16000174 200 200 1
2009-10-14 16000175 200 800 1
2009-10-14 16000176 200 300 1
小计 200 1500 4
2009-10-13 16000176 300 200 1
2009-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