oracle的group by rollup语法应当能解决你的小计与合计的问题。 with T as ( SELECT 'A' x, '1' Y, 3 V FROM DUAL UNION ALL SELECT 'A' x, '1' Y, 3 V FROM DUAL UNION ALL SELECT 'A' x, '2' Y, 3 V FROM DUAL UNION ALL SELECT 'B' x, '1' Y, 3 V FROM DUAL UNION ALL SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL SELECT 'B' x, '1' Y, 3 V FROM DUAL UNION ALL SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL SELECT 'C' x, '2' Y, 3 V FROM DUAL UNION ALL SELECT 'C' x, '1' Y, 3 V FROM DUAL UNION ALL SELECT 'D' x, '51' Y, 3 V FROM DUAL ) -- 上面部分是测试数据,你不需要执行,你只要使用下面的SQL改造下就可以了。 SELECT X,Y,SUM(V) FROM T GROUP BY ROLLUP(X,Y);如果需要美化,可以再加一层SELECTSELECT CASE WHEN X is null THEN'合计' WHEN Y is null THEN '小计('||X||')' ELSE X END X, Y, V2 FROM (SELECT X,Y,SUM(V) V2 FROM T GROUP BY ROLLUP(X,Y));
执行结果X Y V2 ------- -- ---------- A 1 6 A 2 3 小计(A) 9 B 1 6 B 2 9 小计(B) 15 C 1 3 C 2 3 小计(C) 6 D 51 3 小计(D) 3 合计 33
with T as (
SELECT 'A' x, '1' Y, 3 V FROM DUAL UNION ALL
SELECT 'A' x, '1' Y, 3 V FROM DUAL UNION ALL
SELECT 'A' x, '2' Y, 3 V FROM DUAL UNION ALL
SELECT 'B' x, '1' Y, 3 V FROM DUAL UNION ALL
SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL
SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL
SELECT 'B' x, '1' Y, 3 V FROM DUAL UNION ALL
SELECT 'B' x, '2' Y, 3 V FROM DUAL UNION ALL
SELECT 'C' x, '2' Y, 3 V FROM DUAL UNION ALL
SELECT 'C' x, '1' Y, 3 V FROM DUAL UNION ALL
SELECT 'D' x, '51' Y, 3 V FROM DUAL )
-- 上面部分是测试数据,你不需要执行,你只要使用下面的SQL改造下就可以了。
SELECT X,Y,SUM(V) FROM T GROUP BY ROLLUP(X,Y);如果需要美化,可以再加一层SELECTSELECT CASE
WHEN X is null THEN'合计'
WHEN Y is null THEN '小计('||X||')'
ELSE X
END X,
Y,
V2
FROM (SELECT X,Y,SUM(V) V2 FROM T GROUP BY ROLLUP(X,Y));
------- -- ----------
A 1 6
A 2 3
小计(A) 9
B 1 6
B 2 9
小计(B) 15
C 1 3
C 2 3
小计(C) 6
D 51 3
小计(D) 3
合计 33