SQL 连续日期查询在线等，。。。

t101lian 2016-06-22 11:23:09



  

if   object_id('tempdb..#tab') is not null drop table #tab

CREATE TABLE #tab

(

	date_ datetime,

)

INSERT INTO #tab

SELECT '2016-06-02' UNION ALL

SELECT '2016-06-03' UNION ALL

SELECT '2016-06-04' UNION ALL

SELECT '2016-06-08' UNION ALL

SELECT '2016-06-09' UNION ALL

SELECT '2016-06-10' UNION ALL

SELECT '2016-06-13' UNION ALL

SELECT '2016-06-14' UNION ALL

SELECT '2016-06-16'   



SELECT * FROM #tab       



--如何查询出下面的结果

/*

 startdate               enddate

2016-06-02 00:00:00.000	2016-06-04 00:00:00.000

2016-06-08 00:00:00.000	2016-06-10 00:00:00.000

2016-06-13 00:00:00.000	2016-06-14 00:00:00.000

2016-06-16 00:00:00.000	2016-06-16 00:00:00.000

*/

...全文

86 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

中国风 2016-06-22

打赏
举报

SELECT 
MIN(t.date_) AS StartDate
,MAX(t.date_) AS EndDate
FROM 
(SELECT *,grp=DATEADD(dd,-ROW_NUMBER()OVER(ORDER BY date_),date_) FROM #tab) AS t GROUP BY grp

唐诗三百首 2016-06-22

打赏
举报


select 'startdate'=min(date_),
           'enddate'=max(date_)
 from (select date_,rn=datediff(d,'1970-01-01',date_)-row_number() over(order by date_)
           from #tab) t
 group by rn

/*
startdate               enddate
----------------------- -----------------------
2016-06-02 00:00:00.000    2016-06-04 00:00:00.000
2016-06-08 00:00:00.000    2016-06-10 00:00:00.000
2016-06-13 00:00:00.000    2016-06-14 00:00:00.000
2016-06-16 00:00:00.000    2016-06-16 00:00:00.000

(4 row(s) affected)
*/