如何创建递归查询以获取两个日期之间的所有日期 [英] How to create recursive query to get all dates between two dates
本文介绍了如何创建递归查询以获取两个日期之间的所有日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想获取两个日期arrival date
和leaving date
之间的所有日期.
I want to get all dates between two dates arrival date
and leaving date
.
我尝试了该递归查询,但是没有用.
I tried that recursive query but it didn't work.
;with dates as (
SELECT GuestID, ArrivalDate as reserveddate
from dbo.Guest
union all
SELECT GuestID, dateadd (day,1,dbo.Guest. ArrivalDate) as reserveddate
from dbo.Guest
where dateadd (day,1,dbo.Guest. ArrivalDate) <dbo.Guest.leavingate
)
SELECT *
from dates
option (maxrecursion 0)
推荐答案
您需要递归CTE:
DECLARE @arrival_date date = '2016-01-01',
@leaving_date date = '2016-02-01'
;WITH cte AS (
SELECT @arrival_date as date_
UNION ALL
SELECT CAST(DATEADD(day,1,date_) as date)
FROM cte
WHERE date_ < @leaving_date
)
SELECT *
FROM cte
OPTION (MAXRECURSION 0)
输出:
date_
2016-01-01
2016-01-02
2016-01-03
...
2016-01-30
2016-01-31
2016-02-01
EDIT1
根据您的示例:
;WITH cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)
SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)
EDIT2
;WITH Guests AS (
SELECT 1 as GuestID,
'2016-01-01' ArrivalDate,
'2016-01-05' LeavingDate
UNION ALL
SELECT 2 ,
'2016-06-17',
'2016-06-20'
), cte AS (
SELECT GuestID, CAST(ArrivalDate as date) as date_
FROM Guests
UNION ALL
SELECT c.GuestID, CAST(DATEADD(day,1,date_) as date)
FROM cte c
INNER JOIN Guests g
ON g.GuestID = c.GuestID
WHERE date_ < g.LeavingDate
)
SELECT *
FROM cte
ORDER BY GuestID, date_
OPTION (MAXRECURSION 0)
输出:
GuestID date_
1 2016-01-01
1 2016-01-02
1 2016-01-03
1 2016-01-04
1 2016-01-05
2 2016-06-17
2 2016-06-18
2 2016-06-19
2 2016-06-20
这篇关于如何创建递归查询以获取两个日期之间的所有日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文