如何返回多个子集
表Inventory
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
我想返回如下两个记录集：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
合计 347
Item Color Quantity
-------------------- -------------------- --------------------------
Chair Blue 101
Chair Red 210
合计 311
结果分为两个记录集返回而不是一个，语句应该怎样写？
问题点数：50、回复次数：4Top
1 楼dutguoyi（新鲜鱼排）回复于 2006-03-13 17:09:29 得分 5
第一部分
union all
第二部分
这样就连接两个部分了。Top
2 楼xeqtr1982（Visual C# .NET）回复于 2006-03-13 17:17:36 得分 15
--不知道是不是这样，瞎写了一个
declare @Inventory table(Item varchar(10),Color varchar(10),Quantity int)
insert into @Inventory select 'Table','Blue',124
union all select 'Table','Red',223
union all select 'Chair','Blue',101
union all select 'Chair','Red',210
declare @Item varchar(10)
set @Item=''
declare cur_1 cursor for
select distinct Item from @Inventory
open cur_1
fetch next from cur_1 into @Item
while(@@FETCH_STATUS=0)
begin
select * from @Inventory where Item=@Item
union all
select '合计','',sum(Quantity) as Quantity from @Inventory where Item=@Item group by Item
fetch next from cur_1 into @Item
end
close cur_1
DEALLOCATE cur_1Top
3 楼lsqkeke（可可）回复于 2006-03-13 18:02:20 得分 20
同意楼上的！:)
如果Item类型不定就用楼上的游标处理！
如果只是直接得到上面的数据，可以这样：
记录集一：
select Item,Color,Quantity from inventory where Item='Table'
union all
select '合计','',Quantity=sum(Quantity) from inventory where Item='Table'
记录集二：
select Item,Color,Quantity from inventory where Item='Chair'
union all
select '合计','',Quantity=sum(Quantity) from inventory where Item='Chair'
Top
4 楼wgsasd311（自强不息）回复于 2006-03-13 21:57:42 得分 10
--try
select * from (
select Item,Color,Quantity from inventory
union all
select item+'小计:','',sum(quantity) from inventory
group item
union all
select '总计:','',sum(quantity) from inventory
) t
order by itemTop
