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; 

效果如图所示：

 

 

转：http://blog.csdn.net/birds_cerebrum/article/details/73611589

 

参考：https://www.cnblogs.com/Mr-Cxy/p/5923375.html