查询中上下行相减问题，请注意第一行必须为0

Question

问题如下： 表A： 
    
        zyid               bc_date               start_time               end_time 
      02-01             2010-6-6                 17:20:15                 19:36:18 
      02-01             2010-6-6                 18:18:21                 21:03:30 
      02-01             2010-6-7                 03:20:15                 07:36:18 
      02-01             2010-6-7                 09:40:32                 12:50:11 想用一个查询，得到上表中，下一行的 "start_time "   减去   上一行的 "end_time "的值。想得到的效果如下：         zyid               bc_date               start_time               end_time         时间差(分钟) 
      02-01             2010-6-6                 17:20:15                 19:36:18               0 
      02-01             2010-6-6                 18:18:21                 21:03:30             -18   
      02-01             2010-6-7                 03:20:15                 07:36:18             377 
      02-01             2010-6-7                 09:40:32                 12:50:11             127 请问怎样实现呢？请注意，表中第一行查询出来，时间差一定要是0。

Answer 1

select T1.start_time,T.end_time
from (
select row_number() orver(order by bc_date,start_time) as id,*
from tbA
)T,(
select row_number() orver(order by bc_date,start_time) as id,*
from tbA
)T1
where T.id+1=T.id

Answer 2

with play as(
select *,
  [date1] = convert(datetime, rtrim(bc_date) + ' '+ ltrim(start_time)),
  [date2] = convert(datetime, rtrim(bc_date) + ' '+ ltrim(end_time))
  id=row_number() over (order by getdate())
from T )
select  zyid, bc_date, start_time ,end_time,
  [時間差]=datediff(minute, date1,  isnull( (select date2 from play as A where A.id =play.id -1 ), date1) )
from play

Answer 3

if not object_id('tb') is null
drop table tb
Go
Create table tb([zyid] nvarchar(5),[bc_date] Datetime,[start_time] Datetime,[end_time] Datetime)
Insert tb
select N'02-01','2010-6-6','17:20:15','19:36:18' union all
select N'02-01','2010-6-6','18:18:21','21:03:30' union all
select N'02-01','2010-6-7','03:20:15','07:36:18' union all
select N'02-01','2010-6-7','09:40:32','12:50:11'
Go
---2005
;with wufeng4552
as
(select px=row_number()over(order by getdate()),
 *
 from tb)
select zyid,
       bc_date,
       start_time,
       end_time,
       [时间差(分钟)]=isnull(datediff(mi,end_time,(select top 1 start_time
                                                   from wufeng4552
                                                   where px>t.px)),0)from wufeng4552 t
/*
zyid  bc_date                 start_time              end_time                时间差(分钟)
----- ----------------------- ----------------------- ----------------------- -----------
02-01 2010-06-06 00:00:00.000 1900-01-01 17:20:15.000 1900-01-01 19:36:18.000 -78
02-01 2010-06-06 00:00:00.000 1900-01-01 18:18:21.000 1900-01-01 21:03:30.000 -1063
02-01 2010-06-07 00:00:00.000 1900-01-01 03:20:15.000 1900-01-01 07:36:18.000 124
02-01 2010-06-07 00:00:00.000 1900-01-01 09:40:32.000 1900-01-01 12:50:11.000 0
*/

Answer 4

答案好像都不太对啊。
-18是第二行的start_time减去第一行的end_time得来的。

Answer 5

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([zyid] [nvarchar](10),[bc_date] [nvarchar](10),[start_time] [nvarchar](10),[end_time] [nvarchar](10))
INSERT INTO [tb]
SELECT '02-01','2010-6-6','17:20:15','19:36:18' UNION ALL
SELECT '02-01','2010-6-6','18:18:21','21:03:30' UNION ALL
SELECT '02-01','2010-6-7','03:20:15','07:36:18' UNION ALL
SELECT '02-01','2010-6-7','09:40:32','12:50:11'
-->SQL查询如下:
;WITH t AS 
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY zyid ORDER BY bc_date),*
FROM tb 
)
SELECT a.*,ISNULL(DATEDIFF(mi,a.bc_date+' '+a.start_time,b.bc_date+' '+b.end_time),0) 
FROM t a
LEFT JOIN t b 
ON a.zyid=b.zyid
AND a.rn=b.rn+1
/*
rn                   zyid       bc_date    start_time end_time   
-------------------- ---------- ---------- ---------- ---------- -----------
1                    02-01      2010-6-6   17:20:15   19:36:18   0
2                    02-01      2010-6-6   18:18:21   21:03:30   78
3                    02-01      2010-6-7   03:20:15   07:36:18   -377
4                    02-01      2010-6-7   09:40:32   12:50:11   -124(4 行受影响)
*/怎么感觉楼主的结果不太对呢

Answer 6

if not object_id('tb') is null
drop table tb
Go
Create table tb([zyid] nvarchar(5),[bc_date] Datetime,[start_time] Datetime,[end_time] Datetime)
Insert tb
select N'02-01','2010-6-6','17:20:15','19:36:18' union all
select N'02-01','2010-6-6','18:18:21','21:03:30' union all
select N'02-01','2010-6-7','03:20:15','07:36:18' union all
select N'02-01','2010-6-7','09:40:32','12:50:11'
Go
---2005
;with wufeng4552
as
(select px=row_number()over(order by getdate()),
 *
 from tb)
select zyid,
       bc_date,
       start_time,
       end_time,
       [时间差(分钟)]=isnull(datepart(mi,start_time)-datepart(mi,(select top 1 end_time
                                                           from wufeng4552
                                                           where px<t.px)),0)from wufeng4552 t
/*
zyid  bc_date                 start_time              end_time                时间差(分钟)
----- ----------------------- ----------------------- ----------------------- -----------
02-01 2010-06-06 00:00:00.000 1900-01-01 17:20:15.000 1900-01-01 19:36:18.000 0
02-01 2010-06-06 00:00:00.000 1900-01-01 18:18:21.000 1900-01-01 21:03:30.000 -18
02-01 2010-06-07 00:00:00.000 1900-01-01 03:20:15.000 1900-01-01 07:36:18.000 -16
02-01 2010-06-07 00:00:00.000 1900-01-01 09:40:32.000 1900-01-01 12:50:11.000 4
*/

Answer 7

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([zyid] [nvarchar](10),[bc_date] [nvarchar](10),[start_time] [nvarchar](10),[end_time] [nvarchar](10))
INSERT INTO [tb]
SELECT '02-01','2010-6-6','17:20:15','19:36:18' UNION ALL
SELECT '02-01','2010-6-6','18:18:21','21:03:30' UNION ALL
SELECT '02-01','2010-6-7','03:20:15','07:36:18' UNION ALL
SELECT '02-01','2010-6-7','09:40:32','12:50:11'
-->SQL查询如下:
;WITH t AS 
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY zyid ORDER BY bc_date),*
FROM tb 
)
SELECT a.*,-ISNULL(DATEDIFF(mi,a.bc_date+' '+a.start_time,b.bc_date+' '+b.end_time),0) AS 差值 
FROM t a
LEFT JOIN t b 
ON a.zyid=b.zyid
AND a.rn=b.rn+1
/*
rn                   zyid       bc_date    start_time end_time   差值
-------------------- ---------- ---------- ---------- ---------- -----------
1                    02-01      2010-6-6   17:20:15   19:36:18   0
2                    02-01      2010-6-6   18:18:21   21:03:30   -78
3                    02-01      2010-6-7   03:20:15   07:36:18   377
4                    02-01      2010-6-7   09:40:32   12:50:11   124(4 行受影响)
*/这个就和你接近了

Answer 8

;with t
as
(
select ID=ROW_NUMBER() over (order by bc_date),* 
from tb
)
select zyid,[bc_date],[start_time],[end_time],isnull(datediff(N,(select end_time from t where ID=a.ID-1),[start_time]),0)
from  t azyid bc_date start_time end_time (无列名)
02-01 2010-06-06 17:20:15.0000000 19:36:18.0000000 0
02-01 2010-06-06 18:18:21.0000000 21:03:30.0000000 -78
02-01 2010-06-07 03:20:15.0000000 07:36:18.0000000 -1063
02-01 2010-06-07 09:40:32.0000000 12:50:11.0000000 124

Answer 9

你复制一下http://www.huangjie.cn/pictures/ceshi.jpg 这个地址打开，可以看到我的表。

Answer 10

那就这样吧-->SQL查询如下:
;WITH t AS 
(
    SELECT rn=ROW_NUMBER()OVER(PARTITION BY zyid ORDER BY bc_date),*
    FROM tb 
)
SELECT a.*,-ISNULL(DATEDIFF(mi,a.start_time,b.end_time),0) AS 差值 
FROM t a
    LEFT JOIN t b 
        ON a.zyid=b.zyid
        AND a.rn=b.rn+1