关于表查询的问题,知道的指点一下,先谢谢了!
有一个表有2个字段A和B,假设里面内容入下:
A B
1. a 1
2. a 3
3. a 4
4. b 2
5. c 3
6. c 5

问题是要怎么写语句可以输出如下样子得结果:
A B
1. a 8(这里是所有a对应的数的和)
2. a 1
3. a 3
4. a 4
5. b 2(这里是所有b对应的数的和)
6. b 2
7. c 8(这里是所有c对应的数的和)
8. c 3
9. c 5

麻烦知道的指点一下,越简单的语句越好!谢谢了

------解决方案--------------------
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210

下列查询将生成小计报表：

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

Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00



------解决方案--------------------
declare @t table(pk int identity(1,1),A varchar(10),B int)
insert @t select 'a ',1
union all select 'a ',3
union all select 'a ',4
union all select 'b ',2
union all select 'c ',3
union all select 'c ',5

select A,B from
(
select A,B,0 C from @t
union
select A,sum(B),1 C from @t group by A
) a
order by A,C desc