3个字段group by，然后再统计的sql语句的问题！

Question

 
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吧，谢谢了！

Answer 1

啥意思》？这样吗？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  

Answer 2

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 

Answer 3

 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)结果如下:
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

Answer 4

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

Answer 5

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
不好意思，没看清楚啊
这个？？！

Answer 6

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

Answer 7

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
 

Answer 8

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 

Answer 9

--互相学习相互提升
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行。

Answer 10

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

Answer 11

顶一下
  oracle QQ群：54775466
        欢迎您的到来 
               大家一起探讨。