老问题，多级部门（或者种类，行业）分组统计的问题

Question

小LIANG的
DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red',  -23
UNION ALL SELECT 'bb','Cup'  ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red',  -90--统计及排序
SELECT Groups=CASE 
        WHEN GROUPING(Color)=0 THEN Groups
        WHEN GROUPING(Groups)=1 THEN '总计'
        ELSE '' END,
    Item=CASE 
        WHEN GROUPING(Color)=0 THEN Item
        WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
        ELSE '' END,
    Color=CASE 
        WHEN GROUPING(Color)=0 THEN Color
        WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
        ELSE '' END,
    Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
ORDER BY GROUPING(Groups),
    CASE WHEN GROUPING(Groups)=1 THEN '' ELSE Groups END DESC,
    GROUPING(Item),
    CASE WHEN GROUPING(Item)=1 THEN '' ELSE Item END DESC,
    GROUPING(Color),
    CASE WHEN GROUPING(Color)=1 THEN '' ELSE Color END DESC,
    Quantity DESC
/*--结果
Groups Item       Color           Quantity    
--------- -------------- ----------------------- ----------- 
bb     Table      Red             -23
                 Table 小计       -23
bb     Cup       Green           -23
                 Cup 小计        -23
       bb 合计                    -46
aa     Table      Blue            124
                 Table 小计       124
aa     Chair      Red             -90
aa     Chair      Blue            101
                 Chair 小计       11
       aa 合计                    135
总计                              89

Answer 1

在来个你个例子是z老大的
-- 示例数据
DECLARE @v TABLE(
    学号 int, 班级 int, 科目 nvarchar(10), 成绩 int);
INSERT @v
SELECT 1, 1, N'语文', 76  UNION ALL
SELECT 1, 1, N'数学',  87   UNION ALL
SELECT 1, 1, N'英语',  99   UNION ALL
SELECT 2, 1, N'语文', 100   UNION ALL
SELECT 2, 1, N'数学',  56   UNION ALL
SELECT 2, 1, N'英语',  35   UNION ALL
SELECT 3, 2, N'语文', 111   UNION ALL
SELECT 3, 2, N'数学',  90   UNION ALL
SELECT 3, 2, N'英语', 114 SELECT
    班级 = CASE
            WHEN GROUPING(班级) = 1 THEN N'<总计>'
            ELSE RTRIM(班级)
        END,
    科目 = CASE
            WHEN GROUPING(科目) = 0 THEN 科目
            ELSE N'<小计>'
        END,
    总分 = SUM(CASE WHEN 成绩 > 0 THEN 成绩 END),
    人数 = SUM(CASE WHEN 成绩 > 0 THEN 1 END),
    平均 = AVG(CASE WHEN 成绩 > 0 THEN 成绩 END),
    优秀率 = CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 80 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END,
    及格率 = CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 60 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END,
    低分率 = CASE
                WHEN SUM(CASE WHEN 成绩 > 0 THEN 1 END) = 0 THEN 0
                ELSE CONVERT(decimal(4, 2),
                            SUM(CASE WHEN 成绩 > 0 AND 成绩 < 30 THEN 1 END) * 1.
                            / SUM(CASE WHEN 成绩 > 0 THEN 1 END))
            END,
    最高 = MAX(成绩),
    最低 = MIN(CASE WHEN 成绩 > 0 THEN 成绩 END),
    段90 = SUM(CASE WHEN 成绩 >= 90 THEN 1 END),
    段80 = SUM(CASE WHEN 成绩 >= 80 AND 成绩 < 90 THEN 1 END),
    段70 = SUM(CASE WHEN 成绩 >= 70 AND 成绩 < 80 THEN 1 END),
    段60 = SUM(CASE WHEN 成绩 >= 60 AND 成绩 < 70 THEN 1 END),
    段50 = SUM(CASE WHEN 成绩 >= 50 AND 成绩 < 60 THEN 1 END),
    段40 = SUM(CASE WHEN 成绩 >= 40 AND 成绩 < 50 THEN 1 END),
    段30 = SUM(CASE WHEN 成绩 >= 30 AND 成绩 < 40 THEN 1 END),
    段00 = SUM(CASE WHEN 成绩 > 0 AND 成绩 <= 30 THEN 1 END)
FROM @v v_cjb
GROUP BY 班级, 科目 WITH ROLLUP