有如下表结构:
table1
en_name bs_code money1 money2
------- ------- ------ ------
008001 2010101 1000 800
008001 2010102 5000 4000
008001 2010201 23000 4300
008001 2080101 23450 546
007001 2020101 23200 340
007001 2020201 23200 340通过sql查询变成如下汇总结果:
en_name bs_code money1 money2
------- ------- ------ ------
008001 52450 9646
201 29000 9100
20101 6000 4800
2010101 1000 800
2010102 5000 4000
20102 23000 4300
2010201 23000 4300
208 23450 546
20801 23450 546
2080101 23450 546
007001 46400 680
202 46400 680
20201 23200 340
2020101 23200 340
20202 23200 340
2020201 23200 340 黑体部分为汇总结果,请大虾帮忙哦
table1
en_name bs_code money1 money2
------- ------- ------ ------
008001 2010101 1000 800
008001 2010102 5000 4000
008001 2010201 23000 4300
008001 2080101 23450 546
007001 2020101 23200 340
007001 2020201 23200 340通过sql查询变成如下汇总结果:
en_name bs_code money1 money2
------- ------- ------ ------
008001 52450 9646
201 29000 9100
20101 6000 4800
2010101 1000 800
2010102 5000 4000
20102 23000 4300
2010201 23000 4300
208 23450 546
20801 23450 546
2080101 23450 546
007001 46400 680
202 46400 680
20201 23200 340
2020101 23200 340
20202 23200 340
2020201 23200 340 黑体部分为汇总结果,请大虾帮忙哦
insert into test t values('008001','2010101',1000,800);
insert into test t values('008001','2010102',5000,4000);
insert into test t values('008001','2010201',23000,4300);
insert into test t values('008001','2080101',23450,546);
insert into test t values('007001','2020101',23200,340);
insert into test t values('007001','2020201',23200,340);
commit;
(
EN_NAME VARCHAR2(6),
BS_CODE VARCHAR2(10),
MONEY1 NUMBER,
MONEY2 NUMBER
)
如果只有3级的话,也就是bs_code固定为3,5,6这3重的话,明天我试下
select en_name,bs_code,sum(money1),sum(money2)
from test_11
group by rollup(en_name,substr(bs_code,1,3),substr(bs_code,1,5),bs_code) order by en_name desc
有个问题就是bs_code的编号没显示。
SELECT en_name, SUBSTR (bs_code, 1, 3) bs_code, SUM (money1) money1,
SUM (money2) money2
FROM TEST
GROUP BY en_name, SUBSTR (bs_code, 1, 3)
UNION ALL
SELECT en_name, SUBSTR (bs_code, 1, 5) bs_code, SUM (money1) money1,
SUM (money2) money2
FROM TEST
GROUP BY en_name, SUBSTR (bs_code, 1, 5)
UNION ALL
SELECT en_name, NULL bs_code, SUM (money1) money1, SUM (money2) money2
FROM TEST
GROUP BY en_name
UNION ALL
SELECT en_name, bs_code, money1, money2
FROM TEST
ORDER BY 1 DESC, 2 NULLS FIRST结果Row# EN_NAME BS_CODE MONEY1 MONEY21 008001 52450 9646
2 008001 201 29000 9100
3 008001 20101 6000 4800
4 008001 2010101 1000 800
5 008001 2010102 5000 4000
6 008001 20102 23000 4300
7 008001 2010201 23000 4300
8 008001 208 23450 546
9 008001 20801 23450 546
10 008001 2080101 23450 546
11 007001 46400 680
12 007001 202 46400 680
13 007001 20201 23200 340
14 007001 2020101 23200 340
15 007001 20202 23200 340
16 007001 2020201 23200 340
不过出来的结果是这样的Row# EN_NAME BS_CODE SUM(MONEY1) SUM(MONEY2)1 98850 10326
2 008001 6000 4800
3 008001 52450 9646
4 008001 23450 546
5 008001 23450 546
6 008001 2080101 23450 546
7 008001 29000 9100
8 008001 2010101 1000 800
9 008001 2010102 5000 4000
10 008001 2010201 23000 4300
11 008001 23000 4300
12 007001 2020101 23200 340
13 007001 46400 680
14 007001 46400 680
15 007001 23200 340
16 007001 2020201 23200 340
17 007001 23200 340
在你的基础上改了下,用rollup就是要考虑汇总时输出为空的问题,比较麻烦点,另外我看了下,应该是不需要对en_name做汇总的
SELECT DECODE (GROUPING (en_name), 1, '总计', en_name) en_name,
DECODE (GROUPING (en_name),
1, NULL,
DECODE (GROUPING (SUBSTR (bs_code, 1, 3)),
1, NULL,
DECODE (GROUPING (SUBSTR (bs_code, 1, 5)),
1, SUBSTR (bs_code, 1, 3),
DECODE (GROUPING (bs_code),
1, SUBSTR (bs_code, 1, 5),
bs_code
)
)
)
) bs_code,
SUM (money1), SUM (money2)
FROM TEST
GROUP BY en_name,
ROLLUP (SUBSTR (bs_code, 1, 3), SUBSTR (bs_code, 1, 5), bs_code)
ORDER BY en_name DESC, 2 NULLS FIRST
结果Row# EN_NAME BS_CODE SUM(MONEY1) SUM(MONEY2)1 008001 52450 9646
2 008001 201 29000 9100
3 008001 20101 6000 4800
4 008001 2010101 1000 800
5 008001 2010102 5000 4000
6 008001 20102 23000 4300
7 008001 2010201 23000 4300
8 008001 208 23450 546
9 008001 20801 23450 546
10 008001 2080101 23450 546
11 007001 46400 680
12 007001 202 46400 680
13 007001 20201 23200 340
14 007001 2020101 23200 340
15 007001 20202 23200 340
16 007001 2020201 23200 340
en_name bs_code money1 money2
------- ------- ------ ------
008001 52450 9646
201 29000 9100
20101 6000 4800
2010101 1000 800
2010102 5000 4000
20102 23000 4300
2010201 23000 4300
208 23450 546
20801 23450 546
2080101 23450 546
007001 46400 680
202 46400 680
20201 23200 340
2020101 23200 340
20202 23200 340
2020201 23200 340
总计 98850 10326请大虾再帮帮忙。
DECODE (GROUPING (en_name),
1, NULL,
DECODE (GROUPING (SUBSTR (bs_code, 1, 3)),
1, NULL,
DECODE (GROUPING (SUBSTR (bs_code, 1, 5)),
1, SUBSTR (bs_code, 1, 3),
DECODE (GROUPING (bs_code),
1, SUBSTR (bs_code, 1, 5),
bs_code
)
)
)
) bs_code,
SUM (money1), SUM (money2)
FROM TEST
GROUP BY
ROLLUP (en_name,SUBSTR (bs_code, 1, 3), SUBSTR (bs_code, 1, 5), bs_code)
ORDER BY en_name DESC, 2 NULLS FIRST
我最初也是想用rollup的
不过rollup要想出来楼主的结果,就会复杂点,需要通过grouping来做判断进行输出
DECODE (GROUPING (en_name), 1, '总计', en_name) en_name
这句话的意思就是判断当前行是否是按en_name进行汇总统计的行,是的话,显示总计,不是的话显示en_name
用rollup+grouping来做,语句少点,效率估计应该会高点,不过结构会复杂点
我也是在真实环境中写好进行测试通过后再放上来的,不像union
可以直接写好,基本上不用测试就可以贴出来