1.创建测试表:
CREATE TABLE test_ROLLUP_1 (
StateCode CHAR(6),
DepCode CHAR(6),
SendMoney INT
);
2.插入测试语句:
INSERT INTO test_ROLLUP_1
SELECT '100001', '310001', 3000 UNION ALL
SELECT '100001', '310002', 1500 UNION ALL
SELECT '100002', '320001', 4200 UNION ALL
SELECT '100003', '330001', 1800 UNION ALL
SELECT '100003', '330002', 2100 UNION ALL
SELECT '100004', '340001', 2500;
3.使用rollup实现分类汇总功能:
SELECT
IFNULL(StateCode, '合计:') AS StateCode,
IFNULL(DepCode, '小计:') AS DepCode,
SUM(SendMoney) AS SendMoney
FROM
test_ROLLUP_1
GROUP BY
StateCode,
DepCode WITH ROLLUP;