各位,问题如下:表 A 如下: id s_time e_time
101 8:00 9:30 230 10:23 12:15 365 13:30 14:09 125 14:20 16:50 62 17:00 20:20
需要得到的结果:(用下一行的s_time减去上一行的e_time得到时间差(分钟)) id s_time e_time 时间差(分钟)
101 8:00 9:30 0 230 10:23 12:15 53 365 13:30 14:09 75 125 14:20 16:50 11 62 17:00 20:20 10
请问怎样用sql语句实现?
101 8:00 9:30 230 10:23 12:15 365 13:30 14:09 125 14:20 16:50 62 17:00 20:20
需要得到的结果:(用下一行的s_time减去上一行的e_time得到时间差(分钟)) id s_time e_time 时间差(分钟)
101 8:00 9:30 0 230 10:23 12:15 53 365 13:30 14:09 75 125 14:20 16:50 11 62 17:00 20:20 10
请问怎样用sql语句实现?
-- Author : htl258(Tony)
-- Date : 2010-05-18 11:40:10
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [a]
IF OBJECT_ID('[a]') IS NOT NULL
DROP TABLE [a]
GO
CREATE TABLE [a] ([id] [int],[s_time] VARCHAR(10),[e_time] VARCHAR(10))
INSERT INTO [a]
SELECT '101','8:00','9:30' UNION ALL
SELECT '230','10:23','12:15' UNION ALL
SELECT '365','13:30','14:09' UNION ALL
SELECT '125','14:20','16:50' UNION ALL
SELECT '62','17:00','20:20'
-->SQL查询如下:;WITH T AS
(
SELECT RN=row_number()OVER(ORDER BY GETDATE()),*
FROM A
)
SELECT A.id,A.s_time,A.e_time,isnull(DATEDIFF(MI,B.e_time,A.s_time),0) [时间差(分钟)]
FROM T A
LEFT JOIN T B
ON A.RN-1=B.RN
/*
id s_time e_time 时间差(分钟)
----------- ---------- ---------- -----------
101 8:00 9:30 0
230 10:23 12:15 53
365 13:30 14:09 75
125 14:20 16:50 11
62 17:00 20:20 10(5 行受影响)
*/
insert into a values(101 , '08:00', '09:30')
insert into a values(230 , '10:23', '12:15')
insert into a values(365 , '13:30', '14:09')
insert into a values(125 , '14:20', '16:50')
insert into a values(62 , '17:00', '20:20')
goselect t.* ,
[时间差(分钟)] = isnull(datediff(mi, '2000-01-01 ' +(select top 1 e_time from a where s_time < t.s_time order by s_time desc),'2000-01-01 ' + s_time),0)
from a t
drop table a /*
id s_time e_time 时间差(分钟)
----------- ---------- ---------- -----------
101 08:00 09:30 0
230 10:23 12:15 53
365 13:30 14:09 75
125 14:20 16:50 11
62 17:00 20:20 10(所影响的行数为 5 行)
*/