sql如何添加序号
借用一下zjcxc的例子
DECLARE @t TABLE(Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'Table ', 'Blue ',124
UNION ALL SELECT 'Table ', 'Red ', -23
UNION ALL SELECT 'Chair ', 'Blue ',101
UNION ALL SELECT 'Chair ', 'Red ', -90
--统计
SELECT Item,Color,Quantity
FROM(
--明细
SELECT Item,Color,Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT ' ',Item+ ' 合计 ',Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总计 ', ' ',Quantity=SUM(Quantity)
,s1=1,s2= ' ',s3=1
FROM @t
)a ORDER BY s1,s2,s3
/*--结果
Item Color Quantity
-------------- ---------------------- -----------
Chair Blue 101
Chair Red -90
Chair 合计 11
Table Red -23
Table Blue 124
Table 合计 101
总计 112
--*/
/*但我想给结果加个序号*/
/*--结果
id Item Color Quantity
-------------- ---------------------- -----------
1 Chair Blue 101
2 Chair Red -90
Chair 合计 11
1 Table Red -23
2 Table Blue 124
Table 合计 101
总计 112
--*/
谢谢！！
[解决办法]
sql 2005的话, 有row_number()函数就行了
[解决办法]
SELECT Item,Color,Quantity
FROM(
--明细
SELECT identity(int,1,1) as xh,Item,Color,Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT identity(int,1,1) as xh, ' ',Item+ ' 合计 ',Quantity=SUM(Quantity)
,s1=0,s2=Item,s3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总计 ', ' ',Quantity=SUM(Quantity)
,s1=1,s2= ' ',s3=1
FROM @t
)a ORDER BY s1,s2,s3
[解决办法]
SELECT IDentity(int,1,1) 'RowOrder ',Item,Color,Quantity into #tmp
FROM(
--明细
SELECT Item,Color,Quantity=SUM(Quantity),s1=0,s2=Item,s3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT ' ',Item+ ' 合计 ',Quantity=SUM(Quantity),s1=0,s2=Item,s3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总计 ', ' ',Quantity=SUM(Quantity),s1=1,s2= ' ',s3=1
FROM @t
)a ORDER BY s1,s2,s3
select * from #tmp
drop table #tmp
