**功能说明：根据ID分组，或者日期的上一次日期及本次日期和上一次日期的时间间隔
修改说明：Created BY LY 2014-7-18**
修改说明：Created BY LY 2014-7-18**
-----样例
id detail date status
389 1 2014-05-04 17:54:00.000 new
390 2 2014-05-09 00:00:00.000 find
389 2 2014-05-27 17:00:00.000 open
389 3 2014-08-05 14:00:00.000 closed
391 4 2014-07-05 00:00:00.000 new
391 5 2014-07-08 00:00:00.000 closed
400 6 2014-07-01 00:00:00.000 new
400 5 2014-07-08 00:00:00.000 passed
400 9 2014-07-05 00:00:00.000 fixed
400 10 2014-07-02 00:00:00.000 open
400 12 2014-07-10 00:00:00.000 closed
400 13 2013-05-09 00:00:00.000 find
---------想要得到结果
id date [上一次日期] [st] '间隔时间'
389 2014-08-05 2014-05-27 closed 70
389 2014-05-27 2014-05-04 open 23
391 2014-07-08 2014-07-05 closed 3
400 2014-07-10 2014-07-08 closed 2
400 2014-07-08 2014-07-05 passed 3
400 2014-07-05 2014-07-02 fixed 3
400 2014-07-02 2014-07-01 open 1
400 2014-07-01 2013-05-09 new 418
389 2014-05-04 NULL new 0
390 2014-05-09 NULL find 0
391 2014-07-05 NULL new 0
400 2013-05-09 NULL find 0
----样例数据
if OBJECT_ID(N'test',N'U') is not null drop table test
create table test
(
ID int null,
detail int null,
date datetime null,
status varchar(50) null
)
go
insert INTO test
SELECT 389, 1,'2014-05-04 17:54:00.000','new'
UNION ALL
SELECT 390, 2,'2014-05-09 00:00:00.000','find'
UNION ALL
SELECT 389,2,'2014-05-27 17:00:00.000','open'
UNION ALL
SELECT 389,3,'2014-08-05 14:00:00.000','closed'
UNION ALL
SELECT 391, 4,'2014-07-05 00:00:00.000','new'
UNION ALL
SELECT 391, 5,'2014-07-08 00:00:00.000','closed'
UNION ALL
SELECT 400,6,'2014-07-01 00:00:00.000','new'
UNION ALL
SELECT 400,5,'2014-07-08 00:00:00.000','passed'
UNION ALL
SELECT 400,9,'2014-07-05 00:00:00.000','fixed'
UNION ALL
SELECT 400,10,'2014-07-02 00:00:00.000','open'
UNION ALL
SELECT 400,12,'2014-07-10 00:00:00.000','closed'
UNION ALL
SELECT 400,13,'2013-05-09 00:00:00.000','find'
go
select * from test;
--select * from test;
/*
功能说明：根据ID分组，或者日期的，上一次日期及本次日期和上一次日期的时间间隔
修改说明：Created BY LY 2014-7-18
*/
SELECT cte1.id,
cte1.date,
cte2.date AS '上一次日期',
cte1.status,
Datediff(DAY, cte2.date, cte1.date) AS '时间间隔'
FROM (SELECT *,
Row_number() OVER(PARTITION BY ID ORDER BY date DESC) AS RN
FROM test) cte1
INNER JOIN (SELECT *,
Row_number() OVER(PARTITION BY ID ORDER BY date DESC) AS RN
FROM test) cte2
ON cte1.id = cte2.id
AND ( cte1.RN = cte2.RN - 1 )
UNION ALL
SELECT id,
date,
NULL,
status,
'0'
FROM (SELECT *,
Row_number() OVER(PARTITION BY ID ORDER BY date ASC) AS RN
FROM test) A
WHERE RN = 1
本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。
5 个评论
贴一个相关子查询的例子，较梁总的清晰一些。
大概的逻辑是:
1. 外层循环整张表
2. 内层对当前ID 的行进行大小对比，取出上一个日期的行
代码可直接执行。
SELECT *,
(
SELECT TOP 1 [date] FROM test T1
WHERE T1.ID = T.ID AND T1.[date] < T.[date]
ORDER BY T1.[date] DESC
) AS LastDate
FROM test T
ORDER BY ID, T.[date] DESC
大概的逻辑是:
1. 外层循环整张表
2. 内层对当前ID 的行进行大小对比，取出上一个日期的行
代码可直接执行。
SELECT *,
(
SELECT TOP 1 [date] FROM test T1
WHERE T1.ID = T.ID AND T1.[date] < T.[date]
ORDER BY T1.[date] DESC
) AS LastDate
FROM test T
ORDER BY ID, T.[date] DESC
oracle语法
select a.id,a.date,
case
when a.date=lag(a.date,1, a.rdate) over (PARTITION by a.id order by a.date)
then null
else lag(a.date,1, a.date) over (PARTITION by a.id order by a.date)
end lastdate
from test a
select a.id,a.date,
case
when a.date=lag(a.date,1, a.rdate) over (PARTITION by a.id order by a.date)
then null
else lag(a.date,1, a.date) over (PARTITION by a.id order by a.date)
end lastdate
from test a
用leading、lag之类的分析函数就行了啊！