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
SQL 自增列(非自增字段)
最新推荐文章于 2024-07-24 15:35:46 发布