求助大神帮出sql语句！求同一人两行记录的时间间隔天数

[qq_35843704]

有如下数据，想求同一个人第二行记录的开始日期减去上一行记录的结束日期的间隔天数。

想达到如下结果


求助各位大神指点！！！感激不尽！！！！

[Ginnnnnnnn]

WITH table1(姓名,开始日期,结束日期) AS
(
SELECT '张三', CONVERT(DATETIME, '2016/1/10'), CONVERT(DATETIME, '2016/1/15') UNION ALL
SELECT '张三', CONVERT(DATETIME, '2016/1/16'), CONVERT(DATETIME, '2016/1/20') UNION ALL
SELECT '张三', CONVERT(DATETIME, '2016/1/22'), CONVERT(DATETIME, '2016/1/25') UNION ALL
SELECT '李四', CONVERT(DATETIME, '2016/5/1'), CONVERT(DATETIME, '2016/5/10') UNION ALL
SELECT '李四', CONVERT(DATETIME, '2016/5/10'), CONVERT(DATETIME, '2016/5/30') UNION ALL
SELECT '王五', CONVERT(DATETIME, '2016/8/8'), CONVERT(DATETIME, '2016/8/12') UNION ALL
SELECT '王五', CONVERT(DATETIME, '2016/8/14'), CONVERT(DATETIME, '2016/8/17') UNION ALL
SELECT '王五', CONVERT(DATETIME, '2016/8/20'), CONVERT(DATETIME, '2016/8/25') UNION ALL
SELECT '王五', CONVERT(DATETIME, '2016/8/25'), CONVERT(DATETIME, '2016/8/26')
)
SELECT a.*,DATEDIFF(dd,b.结束日期,a.开始日期) 
	FROM table1 a
	LEFT JOIN table1 b ON a.姓名 = b.姓名 AND b.结束日期 < a.结束日期 AND NOT EXISTS(SELECT * FROM table1 WHERE 姓名 = b.姓名 AND 结束日期 > b.结束日期 AND 结束日期 < a.结束日期)

[中国风]

借用楼上数据

with table1(姓名,开始日期,结束日期) as
(
select '张三', convert(datetime, '2016/1/10'), convert(datetime, '2016/1/15') union all
select '张三', convert(datetime, '2016/1/16'), convert(datetime, '2016/1/20') union all
select '张三', convert(datetime, '2016/1/22'), convert(datetime, '2016/1/25') union all
select '李四', convert(datetime, '2016/5/1'), convert(datetime, '2016/5/10') union all
select '李四', convert(datetime, '2016/5/10'), convert(datetime, '2016/5/30') union all
select '王五', convert(datetime, '2016/8/8'), convert(datetime, '2016/8/12') union all
select '王五', convert(datetime, '2016/8/14'), convert(datetime, '2016/8/17') union all
select '王五', convert(datetime, '2016/8/20'), convert(datetime, '2016/8/25') union all
select '王五', convert(datetime, '2016/8/25'), convert(datetime, '2016/8/26')
)
--方法1
select 
	*
	,isnull(RTRIM(DATEDIFF(DD,(SELECT TOP 1 结束日期 FROM table1 WHERE 姓名=A.姓名 AND 开始日期<A.开始日期 ORDER BY 开始日期),A.开始日期) ) ,'') AS 天数
from table1 as a
;

[中国风]

SQL2012以上版本

with table1(姓名,开始日期,结束日期) as
(
select '张三', convert(datetime, '2016/1/10'), convert(datetime, '2016/1/15') union all
select '张三', convert(datetime, '2016/1/16'), convert(datetime, '2016/1/20') union all
select '张三', convert(datetime, '2016/1/22'), convert(datetime, '2016/1/25') union all
select '李四', convert(datetime, '2016/5/1'), convert(datetime, '2016/5/10') union all
select '李四', convert(datetime, '2016/5/10'), convert(datetime, '2016/5/30') union all
select '王五', convert(datetime, '2016/8/8'), convert(datetime, '2016/8/12') union all
select '王五', convert(datetime, '2016/8/14'), convert(datetime, '2016/8/17') union all
select '王五', convert(datetime, '2016/8/20'), convert(datetime, '2016/8/25') union all
select '王五', convert(datetime, '2016/8/25'), convert(datetime, '2016/8/26')
)
--方法2
select 
    *
    ,ISNULL(RTRIM(DATEDIFF(dd,LAG(结束日期)OVER(PARTITION BY 姓名 ORDER BY 开始日期),a.开始日期)),'') AS 天数
from table1 as a
ORDER BY 1,2;
/*
姓名	开始日期	结束日期	天数
李四	2016-05-01 00:00:00.000	2016-05-10 00:00:00.000	
李四	2016-05-10 00:00:00.000	2016-05-30 00:00:00.000	0
王五	2016-08-08 00:00:00.000	2016-08-12 00:00:00.000	
王五	2016-08-14 00:00:00.000	2016-08-17 00:00:00.000	2
王五	2016-08-20 00:00:00.000	2016-08-25 00:00:00.000	3
王五	2016-08-25 00:00:00.000	2016-08-26 00:00:00.000	0
张三	2016-01-10 00:00:00.000	2016-01-15 00:00:00.000	
张三	2016-01-16 00:00:00.000	2016-01-20 00:00:00.000	1
张三	2016-01-22 00:00:00.000	2016-01-25 00:00:00.000	2
*/

[zbdzjx]

with table1(姓名,开始日期,结束日期) as
(
select '张三', convert(datetime, '2016/1/10'), convert(datetime, '2016/1/15') union all
select '张三', convert(datetime, '2016/1/16'), convert(datetime, '2016/1/20') union all
select '张三', convert(datetime, '2016/1/22'), convert(datetime, '2016/1/25') union all
select '李四', convert(datetime, '2016/5/1'), convert(datetime, '2016/5/10') union all
select '李四', convert(datetime, '2016/5/10'), convert(datetime, '2016/5/30') union all
select '王五', convert(datetime, '2016/8/8'), convert(datetime, '2016/8/12') union all
select '王五', convert(datetime, '2016/8/14'), convert(datetime, '2016/8/17') union all
select '王五', convert(datetime, '2016/8/20'), convert(datetime, '2016/8/25') union all
select '王五', convert(datetime, '2016/8/25'), convert(datetime, '2016/8/26')
)
,
table2 as
(
select *, ROW_NUMBER() over(partition by 姓名  order by 开始日期) rn from table1
)
select a.*, convert(int, a.开始日期-b.结束日期) from table2 a left join table2 b on a.姓名=b.姓名 and a.rn=b.rn+1 

[qq_35843704]

不仅仅是这几条，几十万条。该怎么做？

[qq_35843704]

这只是个图例，数据有几十万条。怎么实现呢？

[中国风]

同数据量多少有关系么？ 上面都是方法

[qq_35843704]

sql 2008 怎么做呢？

[中国风]

引用 8 楼 qq_35843704 的回复:

sql 2008 怎么做呢？

用#3方法

[中国风]

提供以下两种方法，可用方法2效率高

--#3方法少了排序这里加上
select 
    *
    ,isnull(RTRIM(DATEDIFF(DD,(SELECT TOP 1 结束日期 FROM table1 WHERE 姓名=A.姓名 AND 开始日期<A.开始日期 ORDER BY 开始日期 DESC),A.开始日期) ) ,'') AS 天数
from table1 as a

--方法2
;WITH CteT1
AS
(
SELECT *,ROW_NUMBER()OVER(PARTITION BY 姓名 ORDER BY 开始日期) AS RN FROM table1
)
SELECT a.*,isnull(RTRIM(DATEDIFF(DD,b.结束日期,a.开始日期) ) ,'') AS 天数
FROM CteT1 AS a 
LEFT JOIN CteT1 AS b ON a.姓名=b.姓名 AND a.RN=b.RN+1