原始表数据
ID code time
--------------------------------------- ----------- ------
134485528 NULL 093445
134485529 NULL 093445
134485530 NULL 093445
134485531 NULL 093446
134485532 NULL 093446
134485533 NULL 093447
134485534 NULL 093447
134485535 NULL 093447
更新后要求结果ID code time
--------------------------------------- ----------- ------
134485528 0 093445
134485529 0 093445
134485530 0 093445
134485531 1 093446
134485532 1 093446
134485533 2 093447
134485534 2 093447
134485535 2 093447
ID code time
--------------------------------------- ----------- ------
134485528 NULL 093445
134485529 NULL 093445
134485530 NULL 093445
134485531 NULL 093446
134485532 NULL 093446
134485533 NULL 093447
134485534 NULL 093447
134485535 NULL 093447
更新后要求结果ID code time
--------------------------------------- ----------- ------
134485528 0 093445
134485529 0 093445
134485530 0 093445
134485531 1 093446
134485532 1 093446
134485533 2 093447
134485534 2 093447
134485535 2 093447
create table #t
(
id int,
name int,
time varchar(10)
)insert into #t values(134485528 , NULL, 093445)
insert into #t values(134485529 , NULL, 093445)
insert into #t values(134485530 , NULL, 093445)
insert into #t values(134485531 , NULL, 093446)
insert into #t values(134485532 , NULL, 093446)
insert into #t values(134485533 , NULL, 093447)
insert into #t values(134485534 , NULL, 093447)
insert into #t values(134485535 , NULL, 093447)with cte as
(
select row_number() over(partition by 1 order by time)-1 as time2 ,time
from
(select distinct time from #t) t
)
update A set A.name=B.time2
from #t A inner join cte B on A.time=B.timeselect * from #tid name time
----------- ----------- ----------
134485528 0 93445
134485529 0 93445
134485530 0 93445
134485531 1 93446
134485532 1 93446
134485533 2 93447
134485534 2 93447
134485535 2 93447(8 行受影响)
drop table #t
go
create table #t
(
id int,
name int,
time varchar(10)
)insert into #t values(134485528 , NULL, 093445)
insert into #t values(134485529 , NULL, 093445)
insert into #t values(134485530 , NULL, 093445)
insert into #t values(134485531 , NULL, 093446)
insert into #t values(134485532 , NULL, 093446)
insert into #t values(134485533 , NULL, 093447)
insert into #t values(134485534 , NULL, 093447)
insert into #t values(134485535 , NULL, 093447);with cte as
(select id,dense_rank() over (order by [time]) as rn from #t
)
update A set A.name=B.rn-1
from #t A inner join cte B on A.id=B.idselect * from #t
code列是根据time前后行进行比较
a>=b a行code值=上一行的值
a<b b行code值=上一行值+1