select
case
when (grouping(ssdept)=1) then '全区'
else isnull(ssdept,'')
end as ssdept,
case
when (grouping(zl)=1) then '小计'
else isnull(zl,'')
end as zl,
sum(fsmoney) as fsmoney ,sum(shmoney) as shmoney ,sum(tkmoney) as tkmoney,sum(jy) as jy from t_luru
where tj='已提交' and fsdate between '2008-01-01' and '2008-05-31'
group by ssdept,zl with rollup;
哪位高手能指点一下，我希望在zl列的中间出现‘小计’，在最后出现‘合计’。
在线等待，解决后直接给分
谢谢了
------解决方案--------------------
- SQL code
DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)INSERT @t SELECT 'aa','Table','Blue', 124UNION ALL SELECT 'bb','Table','Red', -23UNION ALL SELECT 'bb','Cup' ,'Green',-23UNION ALL SELECT 'aa','Chair','Blue', 101UNION 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 @tGROUP BY Groups,Item,Color WITH ROLLUPORDER 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 小计 -23bb Cup Green -23 Cup 小计 -23 bb 合计 -46aa Table Blue 124 Table 小计 124aa Chair Red -90aa Chair Blue 101 Chair 小计 11 aa 合计 135总计 89--*/