CBA

实例讲解SQL Server中-Update-的用法

2019-12-02 16:18:59来源:励志吧0次阅读

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

处理的方法:

--建立测试环境

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

复旦大学附属中山医院预约挂号
广济医院刘志刚
雅安治疗输卵管堵塞方法
杭州治癫痫病那家医院最好
德阳治疗宫颈炎费用
分享到: