mysql中实现分类汇总功能

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;

效果如图所示：