SQL 自增列（非自增字段）

IF object_id('tb') is not null
    drop table tb

create table tb
(
   PROD_ID int,
   name VARCHAR(100)
)
go
INSERT INTO tb
SELECT 111111,'aa'
UNION ALL
SELECT 111112,'bb'
UNION ALL
SELECT 111113,'dd'
UNION ALL
SELECT 111110,'ee'


--Solution 1 :临时表 SQL SERVER 2000
select   identity(int   ,1,1)   as   id   ,*   into   #t   from   tb
select   *   from   #t

--Solution 2: SQL SERVER 2005 不改变表行的顺序
SELECT row_number() over (order by getdate()) as id, *
FROM tb

--Solution 3: SQL SERVER 2005
SELECT row_number() over (order by prod_id) as id, *
FROM tb

--Solution 4: SQL SERVER 2005
SELECT rank() over (order by prod_id) as id, *
FROM tb

--Solution 5: SQL SERVER 2000
select   id=(select   count(*)   from   tb   where   prod_id <=a.prod_id),*   from   tb   a