if object_id('tempdb..#')is not null drop table # go create table #(ID int ,[name] varchar(10) ) insert # select 1,'A' insert # select 3,'B' insert # select 0,'C' select 序號=(select count(*)+1 from # where id<t.id),ID,name from # t order by 序號 /*----------- ----------- ---------- 1 0 C 2 1 A 3 3 B(影響 3 個資料列) */
--sql 2000 select * , px = (select count(*) from tb where id < t.id or (id = t.id and name < t.name)) + 1 from tb t--sql 2005 select * , px = row_number() over(order by id , name) from tb
go
create table #(ID int ,[name] varchar(10) )
insert # select 1,'A'
insert # select 3,'B'
insert # select 0,'C'
select 序號=(select count(*)+1 from # where id<t.id),ID,name from # t order by 序號
/*----------- ----------- ----------
1 0 C
2 1 A
3 3 B(影響 3 個資料列)
*/
select * , px = (select count(*) from tb where id < t.id or (id = t.id and name < t.name)) + 1 from tb t--sql 2005
select * , px = row_number() over(order by id , name) from tb