我有一张表, 表 myTab
--------------------------------
ID ItemName ItemCount
1 book 2
2 book 3
3 pen 3
4 pen 5
想得到的结果集为 book 2
book 3
pen 3
pen 5 book 5
pen 8我想要的结果集= 没分组求和之前的记录 + 按ItemName分组求和的记录
谢谢各位大侠!!!
--------------------------------
ID ItemName ItemCount
1 book 2
2 book 3
3 pen 3
4 pen 5
想得到的结果集为 book 2
book 3
pen 3
pen 5 book 5
pen 8我想要的结果集= 没分组求和之前的记录 + 按ItemName分组求和的记录
谢谢各位大侠!!!
union
select ItemName,sum(ItemCount) from mytab group by ItemName
union all
select ItemName,sum(ItemCount) as ItemCount from tb group by ItemName
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-22 13:05:55
-- Verstion:
-- Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
-- Dec 16 2008 19:46:53
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[ItemName] varchar(4),[ItemCount] int)
insert [tb]
select 1,'book',2 union all
select 2,'book',3 union all
select 3,'pen',3 union all
select 4,'pen',5
--------------开始查询--------------------------
select ItemName,ItemCount from tb
union all
select ItemName,sum(ItemCount) as ItemCount from tb group by ItemName
----------------结果----------------------------
/* ItemName ItemCount
-------- -----------
book 2
book 3
pen 3
pen 5
book 5
pen 8(所影响的行数为 6 行)*/
if object_id('[myTab]') is not null drop table [myTab]
create table [myTab] (ID int,ItemName varchar(4),ItemCount int)
insert into [myTab]
select 1,'book',2 union all
select 2,'book',3 union all
select 3,'pen',3 union all
select 4,'pen',5select itemname,itemcount from [myTab]
union all
select itemname,sum(itemcount) from [myTab]
group by itemname