3个字段group by，去掉统计<200的分组，然后再统计的sql语句的有关问题

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 tmpGROUP 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)) 
 
[解决办法]
顶一下
 oracle QQ群：54775466
       欢迎您的到来  
              大家一起探讨。