老帖子是老帖子老原则,先把测试贴出来:
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呢?
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呢?
SELECT DECODE(GROUPING(riqi) + GROUPING(fee_number),'1','小记','2','合计',z.riqi) riqi,
z.apn,
DECODE(GROUPING(riqi), 0, z.fee_number) fee_number,
SUM(acc_result),
COUNT(*) num
FROM (select z.riqi,z.apn, DECODE(GROUPING(riqi), 0, z.fee_number) fee_number,
z.acc_result, COUNT(*) num from z group by z.fee_number, z.riqi,
z.apn,
z.acc_result having sum(z.acc_result) > 200) 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
按你的描述,很难不通过两次分组来实现
可以
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)不想分组两次的话,可以
SELECT DECODE(GROUPING(riqi)+GROUPING(fee_number),'1','小记','2','合计',riqi) riqi,
apn,
DECODE(GROUPING(riqi),0, fee_number) fee_number,
SUM(acc_result),
COUNT(1) num
FROM z t
WHERE (
SELECT sum(acc_result) FROM z WHERE riqi=t.riqi AND fee_number=t.fee_number AND apn=t.apn)>200
GROUP BY GROUPING SETS((fee_number ,riqi, apn),(fee_number),1)
ORDER BY t.fee_number,
t.riqi,
t.apn
;
不过效率可能会比较低不过从你的结果来看,和你的描述不大一样:不是先分组后,过滤掉金额<=200的记录,而是过滤掉原始表中acc_result<=200的记录:
SELECT DECODE(GROUPING(riqi)+GROUPING(fee_number),'1','小记','2','合计',riqi) riqi,
apn,
DECODE(GROUPING(riqi),0, fee_number) fee_number,
SUM(acc_result),
COUNT(1) num
FROM z t
WHERE acc_result>200
GROUP BY GROUPING SETS((fee_number ,riqi, apn),(fee_number),1)
ORDER BY t.fee_number,
t.riqi,
t.apn
;
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
欢迎您的到来
大家一起探讨。
我是过滤掉分组后 sum(acc_result)<200的记录。