sqlserver grouping函数和with rollup的使用

--使用WITH ROLLUP 生成的结果集显示所选列中值的某一层次结构的聚合，GROUPING()函数判断是否根据当前字段进行了求和的行
SELECT sjxm,
(CASE WHEN GROUPING(sjxm)=1 THEN '总计' WHEN GROUPING(lc)=1 THEN '小计' ELSE MAX(clhp) END) AS clhp,
lc,COUNT(*) AS cs,SUM(lc) AS zlc,
(GROUPING(sjxm)+GROUPING(lc)) AS hjbz 
FROM YZ_MysgTzd
GROUP BY sjxm,lc WITH ROLLUP;