分组统计及字段显示的问题

[ligaru]

表的结构和数据如下:
id name Item Color Quantity
1 11 Table Blue 124
2 22 Table Red 223
3 33 Chair Blue 101
4 44 Chair Red 210
5 55 Table Blue 126
要得到的分组统计及显示字段如下:
Item Color name QtySum
Chair Blue 33 101
Chair Red 44 210
Chair ALL -- 311
Table Blue 11 124
Table Blue 55 126
Table Red 22 223
Table ALL -- 473
ALL ALL -- 784

[zlp321002]

---该问题是一个典型的使用Rollup生成结合的例子,联机帮助也有相关介绍！


-测试环境
declare @Inventory Table (Item varchar(20),Color varchar(20),Quantity money)
insert into @Inventory select 'Table','Blue',124
insert into @Inventory select 'Table','Red' ,223
insert into @Inventory select 'Chair','Blue',101
insert into @Inventory select 'Chair','Red' ,210

--查询
SELECT CASE WHEN (GROUPING(Item) = 1) THEN '总计'
WHEN (GROUPING(Color) = 1) THEN Item+'小计'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN ''
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM @Inventory
GROUP BY Item, Color WITH ROLLUP

--结果
Item Color QtySum
------------------------ -------------------- ---------------------
Chair Blue 101.0000
Chair Red 210.0000
Chair小计 311.0000
Table Blue 124.0000
Table Red 223.0000
Table小计 347.0000
总计 658.0000

（所影响的行数为 7 行）

[zlp321002]

--参考上面的例子........

[paoluo]

RollUp不是特別熟悉


Create Table TEST
(id Int,
name Varchar(10),
Item Varchar(10),
Color Varchar(10),
Quantity Int)
Insert TEST Select 1, '11', 'Table', 'Blue', 124
Union All Select 2, '22', 'Table', 'Red', 223
Union All Select 3, '33', 'Chair', 'Blue', 101
Union All Select 4, '44', 'Chair', 'Red', 210
Union All Select 5, '55', 'Table', 'Blue', 126
Go
Select * From(
Select
(Case When Grouping(Item)=1 Then 'ALL' Else Item End) As Item ,
(Case When Grouping(Color)=1 Then 'ALL' Else Color End) As Color,
(Case When Grouping(Color)=1 Then '--' Else name End) As name,
SUM(Quantity) As QtySum
From TEST
Group By Item,Color,name
With RollUp) A
Where name Is Not Null
Go
Drop Table TEST
GO
--Result
/*
Item Color name QtySum
Chair Blue 33 101
Chair Red 44 210
Chair ALL -- 311
Table Blue 11 124
Table Blue 55 126
Table Red 22 223
Table ALL -- 473
ALL ALL -- 784
*/

[ligaru]

TO zlp321002(龙卷风2006):
我也看了联机帮助,里面显示的是3个字段,我现在要显示的是4个字段.而且不能对name进行统计的.