Sql使用WITH ROLLUP 进行分类汇总及小计功能

最新推荐文章于 2025-03-04 16:03:23 发布

greystar

最新推荐文章于 2025-03-04 16:03:23 发布

阅读量1.7w

点赞数 2

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/greystar/article/details/48973599

版权

SQL 专栏收录该内容

31 篇文章

订阅专栏

效果如下：

测试代码如下：

CREATE TABLE #test
    (
      Name VARCHAR(10) ,
      [procedure] CHAR(1) ,
      model VARCHAR(5) ,
      quantity INT
    );

INSERT INTO #test
        SELECT 'A' ,
                '1' ,
                'φ50' ,
                500
        UNION ALL
        SELECT 'A' ,
                '1' ,
                'φ50' ,
                600
        UNION ALL
        SELECT 'A' ,
                '1' ,
                'φ100' ,
                500
        UNION ALL
        SELECT 'A' ,
                '2' ,
                'φ50' ,
                700
        UNION ALL
        SELECT 'A' ,
                '2' ,
                'φ100' ,
                200
        UNION ALL
        SELECT 'B' ,
                '1' ,
                'φ50' ,
                1000;

SELECT CASE WHEN GROUPING(Name) = 1 THEN '总计'
             WHEN GROUPING(Name) = 0
                  AND GROUPING([procedure]) = 1 THEN Name + '合计'
             WHEN GROUPING(Name) = 0
                  AND GROUPING([procedure]) = 0
                  AND GROUPING([model]) = 1
             THEN Name + '的' + [procedure] + '小计'
             ELSE Name
        END AS Name ,
        CASE WHEN GROUPING([model]) = 1 THEN ''
             ELSE [procedure]
        END AS [procedure] ,
        ISNULL(model, '') AS model ,
        SUM(quantity) AS quantity
FROM    #test
GROUP BY Name ,
        [procedure] ,
        model
        WITH ROLLUP;

DROP TABLE #test;