问题: 表中有两字段:id_no (varchar) , in_date (datetime) ,把in_date 相同的记录的in_date依次累加1秒,
使in_date没有相同的记录。
如以下原始数据:
id_no in_date
5791 2003-9-1 14:42:02
5792 2003-9-1 14:42:02
5794 2003-9-1 14:42:02
5795 2003-9-1 14:42:03
5796 2003-9-1 14:42:03
5797 2003-9-1 14:42:03
5831 2003-9-1 14:42:04
5832 2003-9-1 14:42:14
5833 2003-9-1 14:42:14
得到结果是:
id_no in_date
5791 2003-9-1 14:42:02
5792 2003-9-1 14:42:03
5794 2003-9-1 14:42:04
5795 2003-9-1 14:42:05
5796 2003-9-1 14:42:06
5797 2003-9-1 14:42:07
5831 2003-9-1 14:42:08
5832 2003-9-1 14:42:14
5833 2003-9-1 14:42:15
处理方法:
--
建立测试环境
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排序
select * into # from a order by in_date
-- 相同的时间,加一秒。加完了不带重复的
declare @date1 datetime , @date2 datetime , @date datetime
update #
set @date = case when @date1 = in_date or @date2 >= in_date then dateadd (s, 1 , @date2 ) else in_date end ,
@date1 = in_date,
@date2 = @date ,
in_date = @date
-- 更新到基本表中去
update a set a.in_date = b.in_date from a a join # b on a.id_no = b.id_no
select * from a
drop table #,a
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排序
select * into # from a order by in_date
-- 相同的时间,加一秒。加完了不带重复的
declare @date1 datetime , @date2 datetime , @date datetime
update #
set @date = case when @date1 = in_date or @date2 >= in_date then dateadd (s, 1 , @date2 ) else in_date end ,
@date1 = in_date,
@date2 = @date ,
in_date = @date
-- 更新到基本表中去
update a set a.in_date = b.in_date from a a join # b on a.id_no = b.id_no
select * from a
drop table #,a