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;
效果如图所示:
转:http://blog.csdn.net/birds_cerebrum/article/details/73611589
参考:https://www.cnblogs.com/Mr-Cxy/p/5923375.html