select id=row)number()over(order by getdate()),* from tb
select id=row_number() over (order by getdate()),* from tb
select id=identity(int,1,1),* from tb
select identity(int,1,1) as [自动顺序],Q,W,[名字] into #temp from tbselect * from #temp
提示identity函数只在into语句里使用
select identity(int,1,1) as [自动顺序],Q,W,[名字] into #t from tbselect * from #t
CREATE TABLE #TEMP ( ROWNUM INT IDENTITY, ID NVARCHAR(20) )INSERT INTO #TEMP(ID) SELECT ID FROM TBSELECT tb.*FROM #TEMP left join tb on ......DROP TABLE #TEMP
select id=identity(int,1,1),* into #temp from tbselect * from #temp
select row_number() over (order by getdate()) as [自动顺序],Q,W,名字 from #c as c
create table yourname (Q varchar(2),W varchar(2),名字 varchar(2)) insert into yourname select 'QW','WW','W' union all select 'Q','EE','EE' union all select 'a',null,null union all select 'd',null,null union all select 'u',null,nullalter table yourname add id intdeclare @i int set @i=0 update yourname set @i=id=@i+1select id,Q,W,名字 from yourname /* id Q W 名字 ----------- ---- ---- ---- 1 QW WW W 2 Q EE EE 3 a NULL NULL 4 d NULL NULL 5 u NULL NULL */
update tb set @i=id=@i+1 -- 学习。。
CREATE TABLE t (Q VARCHAR(10), M VARCHAR(10), [名字] VARCHAR(10)) INSERT dbo.t SELECT 'QW', 'WW', 'W' UNION ALL SELECT 'Q', 'EE', 'EE' ALTER TABLE dbo.t ADD [自动顺序] INT IDENTITY(1,1)
select id=row_number() over (order by getdate()),* from tb
select id=identity(int,1,1),* from tb
(
ROWNUM INT IDENTITY,
ID NVARCHAR(20)
)INSERT INTO #TEMP(ID)
SELECT
ID
FROM TBSELECT tb.*FROM #TEMP
left join tb on ......DROP TABLE #TEMP
select row_number() over (order by getdate()) as [自动顺序],Q,W,名字 from #c as c
create table yourname (Q varchar(2),W varchar(2),名字 varchar(2))
insert into yourname
select 'QW','WW','W' union all
select 'Q','EE','EE' union all
select 'a',null,null union all
select 'd',null,null union all
select 'u',null,nullalter table yourname add id intdeclare @i int
set @i=0
update yourname set @i=id=@i+1select id,Q,W,名字 from yourname
/*
id Q W 名字
----------- ---- ---- ----
1 QW WW W
2 Q EE EE
3 a NULL NULL
4 d NULL NULL
5 u NULL NULL
*/
-- 学习。。
INSERT dbo.t
SELECT 'QW', 'WW', 'W' UNION ALL SELECT 'Q', 'EE', 'EE'
ALTER TABLE dbo.t ADD [自动顺序] INT IDENTITY(1,1)
sql2000不支持此函数的。sql2005以上才可以