求一个SQL语句
0
悬赏园豆：100
[已关闭问题]
ID domainname num time
5086 grcwt.mebibu.com 56 2009-8-19 9:35:30
5087 grcwt.mebibu.com 66 2009-8-19 9:40:30
5088 grcwt.mebibu.com 56 2009-8-19 9:45:40
5089 grcwt.mebibu.com 66 2009-8-19 9:50:40
5090 grcwt.mebibu.com 60 2009-8-19 9:51:50
5091 grcwt.mebibu.com 66 2009-8-19 9:52:50
5091 grcwt.mebibu.com 58 2009-8-19 9:52:50
time是时刻的列，需求是Num列中的值小于60的累计分钟数
其他回答(2)
0
没看明白你的意思，是不是这样的。
declare @t table(id int,domainname varchar(20),num int, [time] datetime)
insert @t
select 5086, 'grcwt.mebibu.com', 56, '2009-8-19 9:35:30'
union all
select 5087, 'grcwt.mebibu.com', 66, '2009-8-19 9:40:30'
union all
select 5088, 'grcwt.mebibu.com', 56, '2009-8-19 9:45:40'
union all
select 5089, 'grcwt.mebibu.com', 66, '2009-8-19 9:50:40'
union all
select 5090, 'grcwt.mebibu.com', 60, '2009-8-19 9:51:50'
union all
select 5091, 'grcwt.mebibu.com', 66, '2009-8-19 9:52:50'
union all
select 5091, 'grcwt.mebibu.com', 58, '2009-8-19 9:52:50'
select *,(select isnull(datediff(minute,(select top 1 [time] from @t where id<a.id),time),0)) from @t a where num<60 order by id
0
用datepart把分钟取出来就求和就OK了。
select sum(datepart(ss,Time)) from 表名 where num < 60
0
--算累计时间，应该说清楚开始时间和结束时间啊...这里没说清楚，那我姑且把下一条记录的时间当成结束时间，这样算吧：
select SUM(分钟数) 结束时刻是下一条记录的累计分钟 from
(
select *,
(select isnull(datediff(MINUTE,[time],(select top 1 [time] from 表名 where id>a.id order by id)),0)) [分钟数]
from 表名 a where num<60
) t
