分类汇总GROUPING

declare @t table(DeptNo varchar(10),WorkNo int,salary int)
insert into @t select '电子部',123,2300
union all select '电子部',124,2500
union all select '广告部',125,1200
union all select '广告部',126,1500
union all select '广告部',127,1300

SELECT CASE WHEN (GROUPING(DeptNo) = 1) THEN '总计'
        WHEN (GROUPING(WorkNo) = 1) THEN '小计'
            ELSE ISNULL(DeptNo, 'UNKNOWN')
       END AS WorkNo,
       CASE WHEN (GROUPING(WorkNo) = 1) THEN count(*)
            ELSE ISNULL(WorkNo, 'UNKNOWN')
       END AS WorkNo,
       SUM(salary) AS QtySum
FROM @t
GROUP BY DeptNo, WorkNo WITH ROLLUP