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 個資料列受到影響)

 

*/

 

 

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/wufeng4552/archive/2009/11/25/4871781.aspx