MSSQL編號重排常見解決方案

--> Title : MSSQL編號重排常見解決方案

--> Author : wufeng4552

--> Date : 2009-11-25

if object_id('[tb]') is not null drop table [tb]

go

create table [tb] (ID int,ID1 nvarchar(6),ID2 sql_variant)

insert into [tb]

select 1,'001',null union all

select 2,'001',null union all

select 3,'001',null union all

select 4,'002',null union all

select 5,'002',null union all

select 6,'002',null

--方法1 Update

declare @id1 varchar(3),@id2 int

update [tb] set @id2=case when @id1=id1 then @id2+1 else 1 end,

id2=@id2,@id1=id1

select * from tb

-->查詢結果

/*

ID ID1 ID2

----------- ------ -----

1 001 1

2 001 2

3 001 3

4 002 1

5 002 2

6 002 3

(6 個資料列受到影響)

*/

--方法2 子查詢

update t set

id2=(select count(*) from [tb] where id1=t.id1 and id<=t.id)

from [tb] t

select * from [tb]

-->查詢結果

/*

ID ID1 ID2

----------- ------ -----

1 001 1

2 001 2

3 001 3

4 002 1

5 002 2

6 002 3

(6 個資料列受到影響)

*/

--方法3 臨時表

if object_id('tempdb..#')is not null drop table #

select *,cnt=identity(int,0,1)into # from tb order by id1,id

update a set id2=b.cnt-c.cnt+1

from [tb] a ,# b,

(select id1,min(cnt)cnt from # group by id1)c

where a.id1=b.id1 and a.id=b.id and b.id1=c.id1

select * from tb

-->查詢結果

/*

ID ID1 ID2

----------- ------ -----

1 001 1

2 001 2

3 001 3

4 002 1

5 002 2

6 002 3

(6 個資料列受到影響)

*/

--方法MSSQL2005 cte

;with cte

as

(select *,cnt=row_number()over(partition by ID1 order by (select 1)) from [tb])

update cte set id2=cnt

select * from tb

-->查詢結果

/*

ID ID1 ID2

----------- ------ -----

1 001 1

2 001 2

3 001 3

4 002 1

5 002 2

6 002 3

(6 個資料列受到影響)

*/