SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
order by case when Item='合计' then 2 else 1 end,case when Color='小计' then 2 else 1 end,QtySum
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
order by case when Item='合计' then 2 else 1 end,case when Color='小计' then 2 else 1 end,QtySum
(Item varchar(20),Color varchar(20),Quantity int)insert t
select 'Table','Blue',124 union all
select 'Table','Red',223 union all
select 'Chair','Blue',101 union all
select 'Chair','Red',210
select case when grouping(Item)=1 then '合计'
else isnull(Item,'') end as Item,
case when (grouping(Color)=1 and grouping(Item)<>1) then '小计'
else isnull(Color,'') end as Color,
sum(Quantity) as Quantity
from t
group by Item,Color with rollup
order by Item,Quantitydrop table t
Item Color Quantity
-------------------- -------------------- -----------
Chair Blue 101
Chair Red 210
Chair 小计 311
Table Blue 124
Table Red 223
Table 小计 347
合计 658（所影响的行数为 7 行）