Name Time1 Time2 Time3 Time4
1 2010-08-04 05:06:26 PM 2010-08-04 05:06:29 PM 00:00:03
1 2010-08-04 05:06:33 PM 2010-08-04 09:53:32 PM 04:46:59 04:47:02
1 2010-08-05 09:43:10 AM 2010-08-05 12:43:50 PM 03:00:40
1 2010-08-05 05:43:56 PM 2010-08-05 07:23:33 PM 01:39:37 04:40:11
1 2010-08-06 09:43:56 AM 2010-08-06 02:55:59 PM 05:12:03 05:12:03
1 2010-08-07 09:04:56 AM 2010-08-07 05:43:56 PM 08:39:00 08:39:00
1 2010-08-08 08:56:10 AM
time3=time2-time1
Time4的值是Time3相加（每天），并放在最后一条纪录。
根据Time3的值算出TIME4，
现在Time4的值如何放在每天的最后一条纪录？
--如果time1 time2不是同一天，估计要出问题declare @t table(Name varchar(10),Time1 datetime,Time2 datetime,Time3 varchar(8),Time4 varchar(8) )
insert @t (name,time1,time2)
select '1','2010-08-04 05:06:26 PM','2010-08-04 05:06:29 PM' union all
select '1','2010-08-04 05:06:33 PM','2010-08-04 09:53:32 PM' union all
select '1','2010-08-05 09:43:10 AM','2010-08-05 12:43:50 PM' union all
select '1','2010-08-05 05:43:56 PM','2010-08-05 07:23:33 PM' union all
select '1','2010-08-06 09:43:56 AM','2010-08-06 02:55:59 PM' union all
select '1','2010-08-07 09:04:56 AM','2010-08-07 05:43:56 PM' union all
select '1','2010-08-08 08:56:10 AM',nullupdate @t set time3=right('00'+ltrim(datediff(ss,time1,time2)/3600),2)+':'+
right('00'+ltrim(datediff(ss,time1,time2)%3600/60),2)+':'+
right('00'+ltrim(datediff(ss,time1,time2)%3600%60),2)update t set time4= right('00'+ltrim(datediff(ss,b.time1,b.time2)/3600),2)+':'+
right('00'+ltrim(datediff(ss,b.time1,b.time2)%3600/60),2)+':'+
right('00'+ltrim(datediff(ss,b.time1,b.time2)%3600%60),2)
from @t t , (select name,min(time1)time1,max(time2)time2 from @t group by name,convert(varchar(10),time1,120)) b
where not exists(select 1 from @t where name=t.name and datediff(dd,time1,t.time1)=0 and time2>t.time2)
and datediff(dd,t.time1,b.time1)=0select * from @t/*(7 行受影响)(7 行受影响)
警告: 聚合或其他 SET 操作消除了空值。(5 行受影响)
Name Time1 Time2 Time3 Time4
---------- ----------------------- ----------------------- -------- --------
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 00:00:03 NULL
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 04:46:59 04:47:06
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 03:00:40 NULL
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 01:39:37 09:40:23
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 05:12:03 05:12:03
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 08:39:00 08:39:00
1 2010-08-08 08:56:10.000 NULL NULL NULL(7 行受影响)*/
create table #test(Name int, Time1 datetime,Time2 datetime)
insert #test select 1,'2010-08-04 05:06:26 PM','2010-08-04 05:06:29 PM'
insert #test select 1,'2010-08-04 05:06:33 PM','2010-08-04 09:53:32 PM'
insert #test select 1,'2010-08-05 09:43:10 AM','2010-08-05 12:43:50 PM'
insert #test select 1,'2010-08-05 05:43:56 PM','2010-08-05 07:23:33 PM'
insert #test select 1,'2010-08-06 09:43:56 AM','2010-08-06 02:55:59 PM'
insert #test select 1,'2010-08-07 09:04:56 AM','2010-08-07 05:43:56 PM'
insert #test select 1,'2010-08-08 08:56:10 AM',null
select *,convert(char(8),dateadd(s,datediff(s,Time1,Time2),0),108) as Time3
,case when not exists(select 1 from #test where datediff(day,a.Time1,Time1)=0 and a.Time1<Time1)
then (select convert(char(8),dateadd(s,sum(datediff(s,Time1,Time2)),0),108)
from #test where datediff(day,a.Time1,Time1)=0)
else '' end as Time4
from #test a
Name Time1 Time2 Time3 Time4
----------- ----------------------- ----------------------- -------- --------
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 00:00:03
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 04:46:59 04:47:02
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 03:00:40
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 01:39:37 04:40:17
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 05:12:03 05:12:03
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 08:39:00 08:39:00
1 2010-08-08 08:56:10.000 NULL NULL NULL
警告: 聚合或其他 SET 操作消除了 Null 值。(7 行受影响)
--如果time1 time2不是同一天，估计要出问题declare @t table(Name varchar(10),Time1 datetime,Time2 datetime,Time3 varchar(8),Time4 varchar(8) )
insert @t (name,time1,time2)
select '1','2010-08-04 05:06:26 PM','2010-08-04 05:06:29 PM' union all
select '1','2010-08-04 05:06:33 PM','2010-08-04 09:53:32 PM' union all
select '1','2010-08-05 09:43:10 AM','2010-08-05 12:43:50 PM' union all
select '1','2010-08-05 05:43:56 PM','2010-08-05 07:23:33 PM' union all
select '1','2010-08-06 09:43:56 AM','2010-08-06 02:55:59 PM' union all
select '1','2010-08-07 09:04:56 AM','2010-08-07 05:43:56 PM' union all
select '1','2010-08-08 08:56:10 AM',nullupdate @t set time3=right('00'+ltrim(datediff(ss,time1,time2)/3600),2)+':'+
right('00'+ltrim(datediff(ss,time1,time2)%3600/60),2)+':'+
right('00'+ltrim(datediff(ss,time1,time2)%3600%60),2)update t set time4= right('00'+ltrim(stime/3600),2)+':'+
right('00'+ltrim(stime%3600/60),2)+':'+
right('00'+ltrim(stime%3600%60),2)
from @t t , (select name,convert(varchar(10),time1,120)time1,sum(datediff(ss,time1,time2)) stime from @t group by name,convert(varchar(10),time1,120)) b
where not exists(select 1 from @t where name=t.name and datediff(dd,time1,t.time1)=0 and time2>t.time2)
and datediff(dd,t.time1,b.time1)=0select * from @t/*
Name Time1 Time2 Time3 Time4
---------- ----------------------- ----------------------- -------- --------
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 00:00:03 NULL
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 04:46:59 04:47:02
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 03:00:40 NULL
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 01:39:37 04:40:17
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 05:12:03 05:12:03
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 08:39:00 08:39:00
1 2010-08-08 08:56:10.000 NULL NULL NULL
*/
(name varchar(10),
time1 datetime,
time2 datetime,
time3 varchar(8),
time4 varchar(8),
temp1 int,
temp2 int)insert #temp(name,time1,time2)
select '1','2010-08-04 05:06:26 PM','2010-08-04 05:06:29 PM' union all
select '1','2010-08-04 05:06:33 PM','2010-08-04 09:53:32 PM' union all
select '1','2010-08-05 09:43:10 AM','2010-08-05 12:43:50 PM' union all
select '1','2010-08-05 05:43:56 PM','2010-08-05 07:23:33 PM' union all
select '1','2010-08-06 09:43:56 AM','2010-08-06 02:55:59 PM' union all
select '1','2010-08-07 09:04:56 AM','2010-08-07 05:43:56 PM' union all
select '1','2010-08-08 08:56:10 AM',null--1
declare @time varchar(10),@temp1 int
select @time='',@temp1=0update #temp set temp1=datediff(ss,time1,time2),
@temp1=temp2=case when @time=convert(varchar(10),time1,102) then @temp1+datediff(ss,time1,time2) else datediff(ss,time1,time2) end,
@time=convert(varchar(10),time1,102)
go--2
update #temp set
time3=convert(varchar(2),temp1/3600)+':'+convert(varchar(2),(temp1%3600)/60)+':'+convert(varchar(2),(temp1%3600)%60),
time4=convert(varchar(2),temp2/3600)+':'+convert(varchar(2),(temp2%3600)/60)+':'+convert(varchar(2),(temp2%3600)%60)
go--3
update #temp set time4='' where time1 not in (select min(time1) from #temp group by convert(varchar(10),time1,102))
goselect name,time1,time2,time3,time4 from #temp/*
name time1 time2 time3 time4
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 0:0:3 0:0:3
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 4:46:59
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 3:0:40 3:0:40
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 1:39:37
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 5:12:3 5:12:3
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 8:39:0 8:39:0
1 2010-08-08 08:56:10.000 NULL NULL NULL
*/
--3
update #temp set time4='' where time1 not in (select max(time1) from #temp group by convert(varchar(10),time1,102))
goselect name,time1,time2,time3,time4 from #temp/*
name time1 time2 time3 time4
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 0:0:3
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 4:46:59 4:47:2
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 3:0:40
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 1:39:37 4:40:17
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 5:12:3 5:12:3
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 8:39:0 8:39:0
1 2010-08-08 08:56:10.000 NULL NULL NULL
*/
create table #test(Name int, Time1 datetime,Time2 datetime,Time3 varchar(8),Time4 varchar(8) )insert #test(Name , Time1,Time2) select 1,'2010-08-04 05:06:26 PM','2010-08-04 05:06:29 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-04 05:06:33 PM','2010-08-04 09:53:32 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-05 09:43:10 AM','2010-08-05 12:43:50 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-05 05:43:56 PM','2010-08-05 07:23:33 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-06 09:43:56 AM','2010-08-06 02:55:59 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-07 09:04:56 AM','2010-08-07 05:43:56 PM'
insert #test(Name , Time1,Time2) select 1,'2010-08-08 08:56:10 AM',null;with T AS(
select *,convert(char(8),dateadd(s,datediff(s,Time1,Time2),0),108) as u_Time3
,case when not exists(select 1 from #test where datediff(day,a.Time1,Time1)=0 and a.Time1<Time1)
then (select convert(char(8),dateadd(s,sum(datediff(s,Time1,Time2)),0),108)
from #test where datediff(day,a.Time1,Time1)=0)
else '' end as u_Time4
from #test a
)
update T
SET TIME3=u_Time3,Time4=u_Time4select * from #test
DROP TABLE #test
/*
(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
警告: 聚合或其他 SET 操作消除了 Null 值。(7 行受影响)
Name Time1 Time2 Time3 Time4
----------- ----------------------- ----------------------- -------- --------
1 2010-08-04 17:06:26.000 2010-08-04 17:06:29.000 00:00:03
1 2010-08-04 17:06:33.000 2010-08-04 21:53:32.000 04:46:59 04:47:02
1 2010-08-05 09:43:10.000 2010-08-05 12:43:50.000 03:00:40
1 2010-08-05 17:43:56.000 2010-08-05 19:23:33.000 01:39:37 04:40:17
1 2010-08-06 09:43:56.000 2010-08-06 14:55:59.000 05:12:03 05:12:03
1 2010-08-07 09:04:56.000 2010-08-07 17:43:56.000 08:39:00 08:39:00
1 2010-08-08 08:56:10.000 NULL NULL NULL(7 行受影响)*/
我补充下问题/：
time1,time2,time3数据都已经算出来了，现在是遇到统计time4，有问题。
所以我只想要一条update语句。数据库是mysql，datediff函数不一样。
返回seconds参数，变换成小时、分钟和秒，值以'HH:MM:SS'或HHMMSS格式化，取决于函数是在一个字符串还是在数字上下文中被使用。
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938 TIME_TO_SEC(time)
返回time参数，转换成秒。
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378