Sql Server2005的排名函数:ROW_NUMBER()非常实用,如
select Id,ROW_NUMBER() OVER(ORDER BY ID)
from (
SELECT ID=1
UNION ALL
SELECT 1
UNION ALL
SELECT 3
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 9
) a
得到的结果是
1 1
1 2
2 3
3 4
3 5
9 6
但在Sql Server2000中没有这种排名函数,能否实现row_number()函数呢?
答案是可以的,不过稍微麻烦点。
如下:
CREATE TABLE #tmp(ID INT,ROW INT)
INSERT INTO #tmp(id)
select Id
from (
SELECT ID=1
UNION ALL
SELECT 1
UNION ALL
SELECT 3
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 9
) a
DECLARE @i INT
SET @i=0
UPDATE #tmp
SET @i=@i+1,ROW=@i
SELECT * FROM #tmp