select id,row_number() over(partition by id order by it)num into #t from 表名 delete 表 insert into 表 select * from #t
with cte as (select id,row_number() over(partition by id order by it)num from 表名 delete 表 insert into 表 select * from #t ) update 表 set 表.num=cte.num from 表 inner join cte where 表.id=cte.id
declare @c int=1 while(select count(1) from 表名 where number is null)>0 begin with tb as(select id,number,ROW_NUMBER() over(partition by id order by id)abc from 表名 where number is null) update tb set number=@c where abc=1 set @c=@c+1 end 这么写试试
declare @FGuid uniqueidentifier=null; set @FGuid='B2072A47-B93E-4834-8EA2-023DFAC9A3E8' with cte as ( select FGuid,FID, row_number() over(partition by FGuid order by FGuid)num from FM_ANP_MonthlyPlan_DisplayShelfDetail where FGuid=@FGuid )update A set A.GridOrder=cte.num,A.TaskID=1 from FM_ANP_MonthlyPlan_DisplayShelfDetail A inner join cte on A.FID=cte.FID 这方法可行
不过我表很多 每个都要写个CTE,有更简便的了吗?
with里面能写这么多?还能写删除新增的东西?
这方法测试了下,但是没看的明白,如果我想每次都重新编号,是不是去掉is null的的判断?
create table tb(id int ,num int null) go insert TB select 1,null union all select 1,null union all select 1,null union all select 2,null union all select 2,null union all select 2,null godeclare @t int, @n intupdate tb set @n=case when id=@t then @n+1 else 1 end, num=@n, @t=id go select * from TB /*1 1 1 2 1 3 2 1 2 2 2 3 */ go drop table tb go
delete 表
insert into 表 select * from #t
(select id,row_number() over(partition by id order by it)num from 表名
delete 表
insert into 表 select * from #t )
update 表
set 表.num=cte.num
from 表 inner join cte where 表.id=cte.id
while(select count(1) from 表名 where number is null)>0
begin
with tb as(select id,number,ROW_NUMBER() over(partition by id order by id)abc from 表名
where number is null)
update tb set number=@c where abc=1
set @c=@c+1
end
这么写试试
set @FGuid='B2072A47-B93E-4834-8EA2-023DFAC9A3E8'
with cte as
(
select FGuid,FID,
row_number() over(partition by FGuid order by FGuid)num
from FM_ANP_MonthlyPlan_DisplayShelfDetail
where FGuid=@FGuid
)update A
set A.GridOrder=cte.num,A.TaskID=1
from FM_ANP_MonthlyPlan_DisplayShelfDetail A
inner join cte on A.FID=cte.FID 这方法可行
with里面能写这么多?还能写删除新增的东西?
go
insert TB select 1,null
union all select 1,null
union all select 1,null
union all select 2,null
union all select 2,null
union all select 2,null
godeclare @t int, @n intupdate tb set @n=case when id=@t then @n+1 else 1 end, num=@n, @t=id
go
select * from TB
/*1 1
1 2
1 3
2 1
2 2
2 3
*/
go
drop table tb
go