CREATE TABLE [dbo].[tb] (
[name] [varchar] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb
Select ( ' a ' )
union
select ( ' b ' )
union
select ( ' b ' )
union
select ( ' a ' )
union
select ( ' a ' )
union
select ( ' b ' )
declare @i int
declare @y varchar( 100 )
select identity( int , 1 , 1 ) id, 0 as uid ,name into # from tb order by name
Update # Set uid = Case When name = @y then @i else 1 end,
@i = case When name = @y Then @i + 1 else 1 end, @y = name
id uid name
----------- ----------- --------------------
1 1 a
2 2 a
3 3 a
4 1 b
5 2 b
6 3 b
[name] [varchar] ( 100 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
insert into tb
Select ( ' a ' )
union
select ( ' b ' )
union
select ( ' b ' )
union
select ( ' a ' )
union
select ( ' a ' )
union
select ( ' b ' )
declare @i int
declare @y varchar( 100 )
select identity( int , 1 , 1 ) id, 0 as uid ,name into # from tb order by name
Update # Set uid = Case When name = @y then @i else 1 end,
@i = case When name = @y Then @i + 1 else 1 end, @y = name
id uid name
----------- ----------- --------------------
1 1 a
2 2 a
3 3 a
4 1 b
5 2 b
6 3 b