获取上一行时间减去下一行时间的间隔

Question

如下表：id               url                          date
5738 http://item.taobao.com/auction/   2009-05-08 14:35:29.000
5738 http://item.taobao.com/auction/   2009-05-08 14:28:57.000
5738 http://item.taobao.com/auction/   2009-05-08 14:23:58.000
5738 http://item.taobao.com/auction/   2009-05-08 14:20:20.000
5738 http://shop33653752.taobao.com/   2009-05-08 14:20:12.000
5738 http://item.taobao.com/auction/   2009-05-08 14:20:02.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:50.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:46.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:41.000
5738 http://shop33653752.taobao.com/   2009-05-08 14:19:34.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:26.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:23.000
5738 http://shop33653752.taobao.com/   2009-05-08 14:19:13.000
5738 http://item.taobao.com/auction/   2009-05-08 14:19:03.000我想得到这样的记录
5738 http://item.taobao.com/auction/   67'(这个秒是第一条减第二条的差值，如果大于60秒，显示分1‘20，如果大于60分，显示小时分秒)先谢过~

Answer 1

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,url VARCHAR(31),date DATETIME)
INSERT INTO @T
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:35:29.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:28:57.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:23:58.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:20.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:20:12.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:02.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:50.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:46.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:41.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:34.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:26.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:23.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:13.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:03.000'--SQL查询如下:SELECT
    id,url,
    CONVERT(VARCHAR(10),
            DATEADD(second,
                   DATEDIFF(second,(SELECT TOP 1 date
                                    FROM @T
                                    WHERE A.id=id AND date<A.date
                                     ORDER BY date DESC),date),0),108) AS date
FROM @T AS A/*
id          url                             date
----------- ------------------------------- ----------
5738        http://item.taobao.com/auction/ 00:06:32
5738        http://item.taobao.com/auction/ 00:04:59
5738        http://item.taobao.com/auction/ 00:03:38
5738        http://item.taobao.com/auction/ 00:00:08
5738        http://shop33653752.taobao.com/ 00:00:10
5738        http://item.taobao.com/auction/ 00:00:12
5738        http://item.taobao.com/auction/ 00:00:04
5738        http://item.taobao.com/auction/ 00:00:05
5738        http://item.taobao.com/auction/ 00:00:07
5738        http://shop33653752.taobao.com/ 00:00:08
5738        http://item.taobao.com/auction/ 00:00:03
5738        http://item.taobao.com/auction/ 00:00:10
5738        http://shop33653752.taobao.com/ 00:00:10
5738        http://item.taobao.com/auction/ NULL(14 行受影响)*/

Answer 2

IF OBJECT_ID('TEMPDB..#')IS NOT NULL DROP TABLE #
SET NOCOUNT ON
DECLARE @T TABLE(id INT, url VARCHAR(50), [date] DATETIME) 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:35:29.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:28:57.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:23:58.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:20:20.000' 
INSERT @T SELECT 5738, 'http://shop33653752.taobao.com/'   ,'2009-05-08 14:20:12.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:20:02.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:50.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:46.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:41.000' 
INSERT @T SELECT 5738, 'http://shop33653752.taobao.com/'   ,'2009-05-08 14:19:34.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:26.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:23.000' 
INSERT @T SELECT 5738, 'http://shop33653752.taobao.com/'   ,'2009-05-08 14:19:13.000' 
INSERT @T SELECT 5738, 'http://item.taobao.com/auction/'   ,'2009-05-08 14:19:03.000' 
SELECT *,CNT=IDENTITY(INT,1,1) INTO #  FROM @T ORDER BY [DATE] 
SELECT A.ID,A.URL,A.[DATE],
[時間差]=Right(100   +   DATEDIFF(SS,A.[DATE],B.[DATE])/3600,   2)+': '+ Right(100+DATEDIFF(SS,A.[DATE],B.[DATE])/60,2)+': '+Right(100 +DATEDIFF(SS,A.[DATE],B.[DATE])% 60, 2)
 FROM # A,# B WHERE B.CNT=A.CNT+1  
/*ID          URL                                                DATE                                                   時間差              
----------- -------------------------------------------------- ------------------------------------------------------ ---------------- 
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:03.000                                00: 00: 10
5738        http://shop33653752.taobao.com/                    2009-05-08 14:19:13.000                                00: 00: 10
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:23.000                                00: 00: 03
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:26.000                                00: 00: 08
5738        http://shop33653752.taobao.com/                    2009-05-08 14:19:34.000                                00: 00: 07
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:41.000                                00: 00: 05
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:46.000                                00: 00: 04
5738        http://item.taobao.com/auction/                    2009-05-08 14:19:50.000                                00: 00: 12
5738        http://item.taobao.com/auction/                    2009-05-08 14:20:02.000                                00: 00: 10
5738        http://shop33653752.taobao.com/                    2009-05-08 14:20:12.000                                00: 00: 08
5738        http://item.taobao.com/auction/                    2009-05-08 14:20:20.000                                00: 03: 38
5738        http://item.taobao.com/auction/                    2009-05-08 14:23:58.000                                00: 04: 59
5738        http://item.taobao.com/auction/                    2009-05-08 14:28:57.000                                00: 06: 32*/

Answer 3

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[url] varchar(31),[date] datetime)
insert [tb]
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:35:29.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:28:57.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:23:58.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:20.000' union all
select 5738,'http://shop33653752.taobao.com/','2009-05-08 14:20:12.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:02.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:50.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:46.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:41.000' union all
select 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:34.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:26.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:23.000' union all
select 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:13.000' union all
select 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:03.000'
 
---查询---
select px=identity(int,1,1),* into # from tb order by [date]select 
  a.id,
  a.url,
  a.[date],
  [时间间隔(秒)]=case when datediff(hh,a.[date],b.[date])>0 then ltrim(datediff(ss,a.[date],b.[date])/360)+'小时' else '' end 
               +case when datediff(hh,a.[date],b.[date])>0 or datediff(mi,a.[date],b.[date])>0 then ltrim(datediff(ss,a.[date],b.[date])/60)+'分钟' else '' end
               +ltrim(datediff(ss,a.[date],b.[date])%60)+'秒'
from 
  # a
left join
  # b
on
 a.px=b.px-1drop table #---结果---
/**
id          url                             date                                                   时间间隔(秒)                                        
----------- ------------------------------- ------------------------------------------------------ ---------------------------------------------- 
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:03.000                                10秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:19:13.000                                10秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:23.000                                3秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:26.000                                8秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:19:34.000                                7秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:41.000                                5秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:46.000                                4秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:50.000                                0分钟12秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:20:02.000                                10秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:20:12.000                                8秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:20:20.000                                3分钟38秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:23:58.000                                4分钟59秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:28:57.000                                6分钟32秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:35:29.000                                NULL（所影响的行数为 14 行）
**/

Answer 4

--用一下小梁的，那就这样。
DECLARE @T TABLE (id INT,url VARCHAR(31),date DATETIME)
INSERT INTO @T
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:35:29.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:28:57.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:23:58.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:20.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:20:12.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:20:02.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:50.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:46.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:41.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:34.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:26.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:23.000' UNION ALL
SELECT 5738,'http://shop33653752.taobao.com/','2009-05-08 14:19:13.000' UNION ALL
SELECT 5738,'http://item.taobao.com/auction/','2009-05-08 14:19:03.000'--SQL查询如下:SELECT id,url,CONVERT(VARCHAR(10),DATEADD(second,DATEDIFF(second,
date,(SELECT TOP 1 date FROM @T WHERE A.id=id AND date>A.date ORDER BY date )),0),108) AS date
FROM @T AS A/*
id          url                             date       
----------- ------------------------------- ---------- 
5738        http://item.taobao.com/auction/ NULL
5738        http://item.taobao.com/auction/ 00:06:32
5738        http://item.taobao.com/auction/ 00:04:59
5738        http://item.taobao.com/auction/ 00:03:38
5738        http://shop33653752.taobao.com/ 00:00:08
5738        http://item.taobao.com/auction/ 00:00:10
5738        http://item.taobao.com/auction/ 00:00:12
5738        http://item.taobao.com/auction/ 00:00:04
5738        http://item.taobao.com/auction/ 00:00:05
5738        http://shop33653752.taobao.com/ 00:00:07
5738        http://item.taobao.com/auction/ 00:00:08
5738        http://item.taobao.com/auction/ 00:00:03
5738        http://shop33653752.taobao.com/ 00:00:10
5738        http://item.taobao.com/auction/ 00:00:10（所影响的行数为 14 行）*/

Answer 5

;with list as
(
    select  * from(
        select *,row_number() over(partition by id order by  date desc) as rid from TB                    
    )
tmp 
)
select a.*,datediff(second,a.date,isnull(b.date,a.date) ) as discrepancy from list a left join list b on a.rid=b.rid+1

Answer 6

---查询---
select px=identity(int,1,1),* into # from tb order by [date]select 
  a.id,
  a.url,
  a.[date],
  [时间间隔(秒)]=case when datediff(hh,a.[date],b.[date])>0 then ltrim(datediff(ss,a.[date],b.[date])/360)+'小时' else '' end 
               +case when datediff(ss,a.[date],b.[date])>=360 or datediff(ss,a.[date],b.[date])>=60 then ltrim(datediff(ss,a.[date],b.[date])/60)+'分钟' else '' end
               +ltrim(datediff(ss,a.[date],b.[date])%60)+'秒'
from 
  # a
left join
  # b
on
 a.px=b.px-1drop table #---结果---
/**
id          url                             date                                                   时间间隔(秒)                                        
----------- ------------------------------- ------------------------------------------------------ ---------------------------------------------- 
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:03.000                                10秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:19:13.000                                10秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:23.000                                3秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:26.000                                8秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:19:34.000                                7秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:41.000                                5秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:46.000                                4秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:19:50.000                                12秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:20:02.000                                10秒
5738        http://shop33653752.taobao.com/ 2009-05-08 14:20:12.000                                8秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:20:20.000                                3分钟38秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:23:58.000                                4分钟59秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:28:57.000                                6分钟32秒
5738        http://item.taobao.com/auction/ 2009-05-08 14:35:29.000                                NULL（所影响的行数为 14 行）**/

Answer 7

DECLARE @TB TABLE([id] VARCHAR(4), [url] VARCHAR(31), [date] DATETIME)
INSERT @TB 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:35:29.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:28:57.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:23:58.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:20:20.000' UNION ALL 
SELECT '5738', 'http://shop33653752.taobao.com/', '2009-05-08 14:20:12.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:20:02.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:50.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:46.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:41.000' UNION ALL 
SELECT '5738', 'http://shop33653752.taobao.com/', '2009-05-08 14:19:34.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:26.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:23.000' UNION ALL 
SELECT '5738', 'http://shop33653752.taobao.com/', '2009-05-08 14:19:13.000' UNION ALL 
SELECT '5738', 'http://item.taobao.com/auction/', '2009-05-08 14:19:03.000';WITH YYR AS
(
SELECT *,ID=ROW_NUMBER() OVER (ORDER BY GETDATE()) FROM @TB
)SELECT id,url,date,CASE WHEN HOUR>0 THEN RTRIM(HOUR)+N'小时'+RTRIM(MINUTE)+N'分钟'+rtrim(SECOND)+N'秒'
            ELSE CASE WHEN MINUTE>0 THEN RTRIM(MINUTE)+N'分钟'+rtrim(SECOND)+N'秒'
                      ELSE rtrim(SECOND)+N'秒'
                END
            END AS 时间间隔
FROM (
SELECT *,DIF/3600 AS HOUR,DIF/60 AS MINUTE,DIF%60 AS SECOND
FROM (
SELECT A.*,DATEDIFF(SS,B.date,A.date) AS DIF
FROM YYR AS A LEFT JOIN YYR AS B ON A.ID=B.ID-1
) T
) T
/*
id   url                             date                    时间间隔
---- ------------------------------- ----------------------- -----------------------------------------
5738 http://item.taobao.com/auction/ 2009-05-08 14:35:29.000 6分钟32秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:28:57.000 4分钟59秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:23:58.000 3分钟38秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:20:20.000 8秒
5738 http://shop33653752.taobao.com/ 2009-05-08 14:20:12.000 10秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:20:02.000 12秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:50.000 4秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:46.000 5秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:41.000 7秒
5738 http://shop33653752.taobao.com/ 2009-05-08 14:19:34.000 8秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:26.000 3秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:23.000 10秒
5738 http://shop33653752.taobao.com/ 2009-05-08 14:19:13.000 10秒
5738 http://item.taobao.com/auction/ 2009-05-08 14:19:03.000 NULL(14 row(s) affected)*/