22,210
社区成员
发帖
与我相关
我的任务
分享
update tb set SortNum = (select count(*) from tb where TypeNum = t.TypeNum and id <= t.id) from tb t
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[TypeNum] int,[name] nvarchar(3),[SortNum] int)
Insert tb
select 1,1,N'名称1',null union all
select 2,1,N'名称2',null union all
select 3,1,N'名称3',null union all
select 4,2,N'名称4',null union all
select 5,2,N'名称5',null union all
select 6,3,N'名称6',null
Go
declare @id int,@typenum int
set @id = 0
set @typenum =0
update tb set [SortNum] = case when [TypeNum] = @typenum then @id else [SortNum] end,
@id = case when [TypeNum] = @typenum then @id + 1 else 1 end,
@typenum = [TypeNum]
Select * from tb
/*
id TypeNum name SortNum
----------- ----------- ---- -----------
1 1 名称1 1
2 1 名称2 2
3 1 名称3 3
4 2 名称4 1
5 2 名称5 2
6 3 名称6 1
(6 row(s) affected)
*/
--2000
select
*,SortNum = row_number() over (parition by TypeNum order by getdate())
from
tb
-2005
select
*,SortNum = (select count(*) from tb where TypeNum = t.TypeNum and id <= t.id)
from
tb t
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[TypeNum] int,[name] nvarchar(3),[SortNum] int)
Insert tb
select 1,1,N'名称1',null union all
select 2,1,N'名称2',null union all
select 3,1,N'名称3',null union all
select 4,2,N'名称4',null union all
select 5,2,N'名称5',null union all
select 6,3,N'名称6',null
Go
select id,typeNum,name,SortNum =ROW_NUMBER() over(partition by TypeNum order by id) from tb
id typeNum name SortNum
----------- ----------- ---- --------------------
1 1 名称1 1
2 1 名称2 2
3 1 名称3 3
4 2 名称4 1
5 2 名称5 2
6 3 名称6 1
(6 行受影响)