update语句,相同时间加一秒,高手看过来。
--建立测试环境
create table a(id_no varchar(8),in_date datetime)
go
insert into a select '5791','2003-9-1 14:42:02'
union all select '5792','2003-9-1 14:42:02'
union all select '5794','2003-9-1 14:42:02'
union all select '5795','2003-9-1 14:42:03'
union all select '5796','2003-9-1 14:42:03'
union all select '5797','2003-9-1 14:42:03'
union all select '5831','2003-9-1 14:42:04'
union all select '5832','2003-9-1 14:42:04'
union all select '5833','2003-9-1 14:42:04'
union all select '5734','2003-9-1 14:42:02'
union all select '6792','2003-9-1 14:42:22'
union all select '6794','2003-9-1 14:42:22'
union all select '6795','2003-9-1 14:42:23'
union all select '6796','2003-9-1 14:42:23'
union all select '6797','2003-9-1 14:42:23'
union all select '6831','2003-9-1 14:42:34'
union all select '6832','2003-9-1 14:42:34'
union all select '6833','2003-9-1 14:42:54'
union all select '6734','2003-9-1 14:42:22'
go
要求是in_date字段中相同的时间加1秒,加完了不重复。
1、select * into # from a order by in_date
2、declare @date1 datetime,@date2 datetime,@date datetime
update #
set @date=case when @date2>=in_date or @date1=in_date
then dateadd(s,1,@date2) else in_date end,
@date1=in_date,@date2=@date,in_date=@date
3、update a set a.in_date=b.in_date from
a a join # b on a.id_no=b.id_no
哪位高手解释一下2,就是那个update语句。