Sql Server中Update用法的心得
问题： 表中有两字段：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
处理方法：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--建立测试环境
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifcreate
table a(id_no varchar(8),in_date datetime)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifgo
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifinsert
into a select
'5791','2003-9-1 14:42:02'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5792','2003-9-1 14:42:02'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5794','2003-9-1 14:42:02'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5795','2003-9-1 14:42:03'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5796','2003-9-1 14:42:03'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5797','2003-9-1 14:42:03'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5831','2003-9-1 14:42:04'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5832','2003-9-1 14:42:04'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5833','2003-9-1 14:42:04'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'5734','2003-9-1 14:42:02'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6792','2003-9-1 14:42:22'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6794','2003-9-1 14:42:22'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6795','2003-9-1 14:42:23'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6796','2003-9-1 14:42:23'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6797','2003-9-1 14:42:23'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6831','2003-9-1 14:42:34'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6832','2003-9-1 14:42:34'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6833','2003-9-1 14:42:54'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifunion
all
select
'6734','2003-9-1 14:42:22'
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifgo
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--生成临时表，按照in_date排序
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifselect
*
into # from a order
by in_date
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--相同的时间，加一秒。加完了不带重复的
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifdeclare
@date1
datetime,@date2
datetime,@date
datetime
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifupdate #
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifset
@date=case
when
@date1=in_date or
@date2>=in_date then
dateadd(s,1,@date2) else in_date end,
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif @date1=in_date,
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif @date2=@date,
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif in_date=@date
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--更新到基本表中去
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifupdate a set a.in_date=b.in_date from a a join # b on a.id_no=b.id_no
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifselect
*
from a
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifdrop
table #,a
页:
[1]