select *,RowNumber() over(order by id) as seq from Table
if object_id('tb')is not null drop table tb go create table tb(id int, name varchar(10)) insert tb select 90 ,'kkk' insert tb select 780 , 'uuu' insert tb select 987, 'iii' select *,cnt=(select count(*) from tb where id<=t.id) from tb t /*id name cnt ----------- ---------- ----------- 90 kkk 1 780 uuu 2 987 iii 3*/
应该是 select *,ROW_NUMBER() over(order by id) as seq from Table
if not object_id('TB') is null drop table TB Go Create table TB([id] int,[name] nvarchar(3)) Insert TB select 90,N'kkk' union all select 780,N'uuu' union all select 987,N'iii' GoSELECT *,IDNO=IDENTITY(INT,1,1) INTO # FROM TB SELECT * FROM # /* id name IDNO ----------- ---- ----------- 90 kkk 1 780 uuu 2 987 iii 3(所影响的行数为 3 行) */
create table #RR ( id int, [name] varchar(20) )insert into #RR select 90,'KKK' union all select 780,'uuu' union all select 987,'iii'select *,row_number() over (order by id asc) sep from #RR
好像在java那里看见过你 create table test(id int, name varchar(20)) insert test select 90,'kkk' union all select 780,'uuu' union all select 987,'iii' go select * from test id name ----------- -------------------- 90 kkk 780 uuu 987 iii(所影响的行数为 3 行) select id,name,seq=(select count(*) from test where id<=t.id)from test t id name seq ----------- -------------------- ----------- 90 kkk 1 780 uuu 2 987 iii 3(所影响的行数为 3 行)
select *,RowNumber() over(order by id) as seq from table
select *,row_number() over (order by id asc) sep from tb
select *,identity(int,1,1) as seq from 表名
select *,row_number()over(partition by id order by id) as seq from table
go
create table tb(id int, name varchar(10))
insert tb select 90 ,'kkk'
insert tb select 780 , 'uuu'
insert tb select 987, 'iii'
select *,cnt=(select count(*) from tb where id<=t.id) from tb t
/*id name cnt
----------- ---------- -----------
90 kkk 1
780 uuu 2
987 iii 3*/
select *,ROW_NUMBER() over(order by id) as seq from Table
drop table TB
Go
Create table TB([id] int,[name] nvarchar(3))
Insert TB
select 90,N'kkk' union all
select 780,N'uuu' union all
select 987,N'iii'
GoSELECT *,IDNO=IDENTITY(INT,1,1) INTO # FROM TB
SELECT * FROM # /*
id name IDNO
----------- ---- -----------
90 kkk 1
780 uuu 2
987 iii 3(所影响的行数为 3 行)
*/
(
id int,
[name] varchar(20)
)insert into #RR select 90,'KKK'
union all select 780,'uuu'
union all select 987,'iii'select *,row_number() over (order by id asc) sep from #RR
create table test(id int, name varchar(20))
insert test select 90,'kkk'
union all select 780,'uuu'
union all select 987,'iii'
go
select * from test
id name
----------- --------------------
90 kkk
780 uuu
987 iii(所影响的行数为 3 行)
select id,name,seq=(select count(*) from test where id<=t.id)from test t
id name seq
----------- -------------------- -----------
90 kkk 1
780 uuu 2
987 iii 3(所影响的行数为 3 行)
select *,RowNumber() over(order by id) as seq from table
select *,row_number()over(partition by id order by id) as seq
from table