34,588
社区成员
发帖
与我相关
我的任务
分享
[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code
update
tb
set
date1 = '17:00'
from
tb t
where
date1
between
'16:20' and '17:00'
and
not exists(select 1 from tb where 用户ID = t.用户ID and date1>t.date1 and date1 between '16:20' ……
[/Quote]
严重同意。
create table tab ([key] int identity(1,1),date1 smalldatetime,username varchar(10))
insert tab
select getdate(),'111' union all
select getdate(),'222' union all
select getdate(),'333'
select * from tab
--数据刚好
/*
key date1 username
----------- ------------------------------------------------------ ----------
15 2010-04-30 16:16:00 333
16 2010-04-30 16:26:00 111
17 2010-04-30 16:26:00 222
18 2010-04-30 16:26:00 333
19 2010-04-30 16:26:00 111
20 2010-04-30 16:26:00 222
21 2010-04-30 16:26:00 111
22 2010-04-30 16:27:00 111
23 2010-04-30 16:27:00 111
24 2010-04-30 16:27:00 111
25 2010-04-30 16:27:00 222
26 2010-04-30 16:27:00 111
27 2010-04-30 16:27:00 222
28 2010-04-30 16:27:00 333
(所影响的行数为 28 行)
*/
-- 先用select看看结果是否正确
select * from tab t1
where [key] = (
select top 1 t2.[key] from tab t2
where t2.date1 = (
select max(t3.date1) from tab t3
where t2.username=t3.username and convert(datetime,t3.date1) between left(getdate(),10)+' 16:20:00' and left(getdate(),10)+' 17:00:00'
) and t1.username=t2.username
)
/*
key date1 username
----------- ------------------------------------------------------ ----------
22 2010-04-30 16:27:00 111
25 2010-04-30 16:27:00 222
28 2010-04-30 16:27:00 333
(所影响的行数为 3 行)
*/
--再换update
update t1
set t1.date1=left(t1.date1,10)+' 17:00:00'
from tab t1
where [key] = (
select top 1 t2.[key] from tab t2
where t2.date1 = (
select max(t3.date1) from tab t3
where t2.username=t3.username and convert(datetime,t3.date1) between left(getdate(),10)+' 16:20:00' and left(getdate(),10)+' 17:00:00'
) and t1.username=t2.username
)
/*(所影响的行数为 3 行)
*/
select * from tab
/*
20 2010-04-30 16:26:00 222
21 2010-04-30 16:26:00 111
22 2010-04-30 17:00:00 111
23 2010-04-30 16:27:00 111
24 2010-04-30 16:27:00 111
25 2010-04-30 17:00:00 222
26 2010-04-30 16:27:00 111
27 2010-04-30 16:27:00 222
28 2010-04-30 17:00:00 333
(所影响的行数为 28 行)*/
create table tb(date1 datetime,username int)
insert into tb values('2010-04-30 16:21:00' , 1)
insert into tb values('2010-04-30 16:11:00' , 1)
insert into tb values('2010-04-30 16:22:00' , 1)
insert into tb values('2010-04-30 16:21:00' , 1)
insert into tb values('2010-04-30 16:35:00' , 1)
insert into tb values('2010-04-30 16:45:00' , 1)
insert into tb values('2010-04-30 16:21:00' , 2)
insert into tb values('2010-04-30 16:11:00' , 2)
insert into tb values('2010-04-30 16:22:00' , 2)
insert into tb values('2010-04-30 16:21:00' , 2)
insert into tb values('2010-04-30 16:35:00' , 2)
insert into tb values('2010-04-30 16:45:00' , 2)
insert into tb values('2010-04-29 16:21:00' , 1)
insert into tb values('2010-04-29 16:11:00' , 1)
insert into tb values('2010-04-29 16:22:00' , 1)
insert into tb values('2010-04-29 16:21:00' , 1)
insert into tb values('2010-04-29 16:35:00' , 1)
insert into tb values('2010-04-29 16:45:00' , 1)
go
--方法一
update tb set date1 = convert(varchar(10),date1,120) + ' 17:00:00' from tb t
where convert(varchar(5),date1,108) between '16:20' and '17:00' and date1 =
(select max(date1) from tb where convert(varchar(5),date1,108) between '16:20' and '17:00' and username = t.username and convert(varchar(10),date1,120) = convert(varchar(10),t.date1,120))
--方法二
update tb set date1 = convert(varchar(10),date1,120) + ' 17:00:00' from tb t
where convert(varchar(5),date1,108) between '16:20' and '17:00' and not exists
(select 1 from tb where convert(varchar(5),date1,108) between '16:20' and '17:00' and username = t.username and convert(varchar(10),date1,120) = convert(varchar(10),t.date1,120) and date1 > t.date1)
select * from tb
drop table tb
/*
date1 username
------------------------------------------------------ -----------
2010-04-30 16:21:00.000 1
2010-04-30 16:11:00.000 1
2010-04-30 16:22:00.000 1
2010-04-30 16:21:00.000 1
2010-04-30 16:35:00.000 1
2010-04-30 17:00:00.000 1
2010-04-30 16:21:00.000 2
2010-04-30 16:11:00.000 2
2010-04-30 16:22:00.000 2
2010-04-30 16:21:00.000 2
2010-04-30 16:35:00.000 2
2010-04-30 17:00:00.000 2
2010-04-29 16:21:00.000 1
2010-04-29 16:11:00.000 1
2010-04-29 16:22:00.000 1
2010-04-29 16:21:00.000 1
2010-04-29 16:35:00.000 1
2010-04-29 17:00:00.000 1
(所影响的行数为 18 行)
*/
update tb set date1 = convert(varchar(10),date1,120) + ' 17:00:00' from tb t
where convert(varchar(5),date1,108) between '16:20' and '17:00' and date1 =
(select max(date1) from tb where convert(varchar(5),date1,108) between '16:20' and '17:00' and username = t.username and convert(varchar(10),date1,120) = convert(varchar(10),t.date1,120))
update tb set date1 = convert(varchar(10),date1,120) + ' 17:00:00' from tb t
where convert(varchar(5),date1,108) between '16:20' and '17:00' and not exists
(select 1 from tb where convert(varchar(5),date1,108) between '16:20' and '17:00' and username = t.username and convert(varchar(10),date1,120) = convert(varchar(10),t.date1,120) and date1 > t.date1)
update tb set date1 = convert(varchar(10),date1,120) + ' 17:00:00' from tb t
where convert(varchar(5),date1,108) between '16:20' and '17:00' and date1 =
(select max(date1) from tb where convert(varchar(5),date1,108) between '16:20' and '17:00' and username = t.username and convert(varchar(10),date1,120) = convert(varchar(10),t.date1,120))
??
UPDATE a SET date1=convert(varchar(11),date1,120)+' 17:00:00' FROM [Table] a
WHERE NOT exists(SELECT 1 FROM [Table] WHERE 用户名=a.用户名
and convert(varchar(20),date1,120) between convert(VARCHAR(11),date1,120)+'16:20:00' AND convert(VARCHAR(11),date1,120)+'17:00:00'
AND convert(varchar(20),date1,120)> convert(varchar(20),a.date1,120)
)
AND convert(varchar(8),date1,108) BETWEEN '16:20:00' AND '17:00:00'
update
tb
set
date1 = '17:00'
from
tb t
where
date1
between
'16:20' and '17:00'
and
not exists(select 1 from tb where 用户ID = t.用户ID and date1>t.date1 and date1 between '16:20' and '17:00' )
update tb set date1 = '17:00' from tb t where date1 between '16:20' and '17:00' and date1 =
(select max(date1) from tb where date1 between '16:20' and '17:00' and 用户ID = t.用户ID )