select [num]=identity(int,1,1),* into #--查询
select [ID]=(select count(1) from # where NAMES=t.NAMES and num<=t.num),
NAMES
from # t
select [ID]=(select count(1) from # where NAMES=t.NAMES and num<=t.num),
NAMES
from # t
(
ID int,
NAMES varchar(10)
)
insert @tb
select 1,'S' union
select 2,'S' union
select 3,'S' union
select 4,'S' union
select 1,'A' union
select 1,'B' union
select 1,'C' union
select 2,'B' select [num]=identity(int,1,1),* into # from @tb--查询
select [ID]=(select count(1) from # where NAMES=t.NAMES and num<=t.num),
NAMES
from # t order by NAMES--结果
/*
ID NAMES
----------- ----------
1 A
1 B
2 B
1 C
1 S
2 S
3 S
4 S(8 row(s) affected)
*/
truncate table 表
insert 表(ID,NAMES)
select (select count(1) from # where NAMES=t.NAMES and num<=t.num),
NAMES
from # t
alter table 表 add num int identity
go
update 表
set ID=(select count(1) from 表 where NAMES=t.NAMES and num<=t.num)
from 表 t--删除identity列
alter table 表 drop column num