grouping分级汇总用法

declare @t table(DeptNo varchar(20),WorkNo int,salary int)
insert @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
DeptNo=case when grouping(DeptNo)=1 then '总计'
     when grouping(workNo)=1 then '合计'
            else DeptNo end,
WorkNo=case when grouping(workNo)=1 then count(1)
            else WorkNo end,
sum(salary) as salary
from @t a
group by  DeptNo,WorkNo
with rollup