3个字段group by，去掉统计<200的分组，然后再统计的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;


现在有新要求了，就是在原来基础上实现 去掉统计<200的分组，要求达到如下效果：


这种sql如何写啊，我自己用一个group by语句搞不定，我同事写了一个，但是group by了2次。问下大家有没有group by一次就可以搞定的sql呢？



------解决方案--------------------
好复杂，得静心慢慢做
------解决方案--------------------
用group by 和in子句试试！！！！！！！！！！！
------解决方案--------------------
group by2次做出来挺有想法的
------解决方案--------------------
汗..怎么跟我写的这么像..
按你的描述，很难不通过两次分组来实现
可以

SQL code

WITH tmp AS
  (SELECT riqi,
    apn,
    fee_number,
    SUM(acc_result) acc_result,
    COUNT(1) num
  FROM z
  GROUP BY riqi,
    fee_number,
    apn
  HAVING SUM(acc_result)>200
  )
SELECT
  CASE
    WHEN GROUPING(riqi)=0
    THEN riqi
    WHEN GROUPING(fee_number)=0
    THEN '小计'
    ELSE '合计'
  END riqi ,
  apn ,
  DECODE(GROUPING(riqi),0,fee_number)fee_number,
  SUM(acc_result),
  SUM(num)
FROM tmp
GROUP BY GROUPING SETS((riqi,apn,fee_number),fee_number,1)

------解决方案--------------------
   关注下！

------解决方案--------------------

SQL code
SELECT decode(grouping_id(riqi, fee_number),0,riqi,2,'小计',3,'总计') riqi,
       apn,fee_number,sum(acc_result),count(1)
  FROM (SELECT z.riqi,
               z.apn,
               z.fee_number,
               SUM(z.acc_result) acc_result,
               COUNT(1) num
          FROM z
         GROUP BY z.fee_number, z.riqi, z.apn
        HAVING SUM(z.acc_result) > 200
         ORDER BY z.fee_number, z.riqi, z.apn)
 GROUP BY ROLLUP(fee_number, (riqi, apn))