Oracle数据查询与统计汇总技巧

SELECT
CASE WHEN text IS NOT NULL THEN text ELSE '总计' END text,
CASE WHEN text IS NOT NULL THEN id ELSE 0 END id,
SUM(total_num) AS total_num
FROM (SELECT text,
(CASE
WHEN ID = 1 THEN 1
WHEN ID = 2 THEN 2
WHEN ID = 3 THEN 4
WHEN ID = 4 THEN 4
WHEN ID = 5 THEN 5
WHEN ID = 6 THEN 4
WHEN ID = 7 THEN 4
END) id,
SUM(total_num) total_num
FROM (SELECT aa.id, aa.text, 1 total_num
FROM (SELECT 1 id,'AAA' text FROM dual
UNION
SELECT 2 id,'BBB' text FROM dual
UNION
SELECT 3 id,'CCC' text FROM dual
UNION
SELECT 4 id,'CCC' text FROM dual
UNION
SELECT 6 id,'CCC' text FROM dual
UNION
SELECT 7 id,'CCC' text FROM dual
UNION
SELECT 5 id,'DDD' text FROM dual
)aa
)
GROUP BY text,(CASE
WHEN id = 1 THEN 1
WHEN id = 2 THEN 2
WHEN id = 3 THEN 4
WHEN id = 4 THEN 4
WHEN id = 5 THEN 5
WHEN id = 6 THEN 4
WHEN id = 7 THEN 4
END)
ORDER BY id
) A
GROUP BY rollup(text,id)
HAVING text IS NULL OR id IS NOT NULL