mysql中实现分类汇总功能

1.创建测试表：

CREATE TABLE test_ROLLUP_1 (  StateCode  CHAR(6),  DepCode   CHAR(6),  SendMoney  INT);

2.插入测试语句：

INSERT INTO test_ROLLUP_1SELECT '100001',  '310001',  3000  UNION ALLSELECT '100001',  '310002',  1500  UNION ALLSELECT '100002',  '320001',  4200  UNION ALLSELECT '100003',  '330001',  1800  UNION ALLSELECT '100003',  '330002',  2100  UNION ALLSELECT '100004',  '340001',  2500;

3.使用rollup实现分类汇总功能：

SELECTIFNULL(StateCode, '合计:') AS StateCode,IFNULL(DepCode, '小计:') AS DepCode,SUM(SendMoney) AS SendMoneyFROMtest_ROLLUP_1GROUP BYStateCode,DepCode WITH ROLLUP;

效果如图所示：