sql server 2005 有内置的函数 row_number() 示例:select row_number() OVER(order by employeeid) from dbo.Employees
declare @tb table(a int , b int , c int) insert into @tb select 3,3,3 insert into @tb select 2,2,2 insert into @tb select 4,4,4 insert into @tb select 6,6,6select * ,[order]=row_number() over(order by a) from @tba b c order 2 2 2 1 3 3 3 2 4 4 4 3 6 6 6 4
--生成整数序列 ;WITH L0 AS(SELECT 1 AS c UNION ALL SELECT 1), L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B), L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B), L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B), L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B), L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B), Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5) SELECT n FROM Nums WHERE n <= 100000 --生成序号的话 2000中 identity(int,1,1)借助临时表 2005中 row_number() over(order by id)
示例:select row_number() OVER(order by employeeid) from dbo.Employees
insert into @tb select 3,3,3
insert into @tb select 2,2,2
insert into @tb select 4,4,4
insert into @tb select 6,6,6select * ,[order]=row_number() over(order by a)
from @tba b c order
2 2 2 1
3 3 3 2
4 4 4 3
6 6 6 4
;WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= 100000 --生成序号的话
2000中 identity(int,1,1)借助临时表
2005中 row_number() over(order by id)