老帖子是老帖子
老原则，先把测试贴出来：
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))