获取SQL两个日期之间的日期列表

问题描述：

查询

select * from Vacation

输出

EmpID VStart  VacEnd  VacType  PostDate 
362330 2017-10-15 2017-10-19 Sick Leave 2017-10-15

列出两个日期之间的日期所需输出

EmpID Date  VacType  
362330 2017-10-15 Sick Leave 
362330 2017-10-16 Sick Leave 
362330 2017-10-17 Sick Leave 
362330 2017-10-18 Sick Leave 
362330 2017-10-19 Sick Leave

我曾尝试使用下面的查询以获得所需的输出，但它没有工作，要做到这一点是创建一个数字表或使用子查询或CTE得到

DECLARE @Start DATETIME, @End DATETIME 
SELECT @Start='2017-10-15' , @End = '2017-10-19' 
;WITH DateList 
AS 
(
SELECT @Start [Date] 
UNION ALL 
SELECT [Date] +1 FROM DateList WHERE [Date] <@End 
) 
SELECT dbo.Vacation.EmpID,[Date], dbo.Vacation.VacType FROM dbo.Vacation INNER JOIN DateList ON DateList.Date = dbo.Vacation.VStart

为什么你已经在列中已经有了这些值的时候声明了“@ Start”和“@ End”？ –

答

最简单的方法一个数字列表，然后根据休假的开始和结束日期加入您的休假表。

例如，

DECLARE @Vacation TABLE (EmpID INT, VStart DATE, VacEnd DATE, VacType VARCHAR(255)); 
INSERT @Vacation (EmpID, VStart, VacEnd, VacType) VALUES 
(1, '2017-10-15', '2017-10-19', 'Sick Leave'), 
(2, '2017-10-15', '2017-10-16', 'Super Fun Happy Vacation'); 

SELECT Vacation.EmpID, 
     [Date] = DATEADD(DAY, T.N, Vacation.VStart), 
     Vacation.VacType 
FROM (
    SELECT N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 
    FROM sys.objects 
) AS T 
JOIN @Vacation AS Vacation 
    ON DATEDIFF(DAY, Vacation.VStart, Vacation.VacEnd) >= T.N;

答

我刚才所作变化不大，在查询中，我没有测试过，但似乎它会工作，请尝试。

DECLARE @Start DATETIME, @End DATETIME 
SELECT @Start='2017-10-15' , @End = '2017-10-19' 
;WITH DateList 
AS 
(
SELECT @Start AS [Date],@Start AS StartDate 
UNION ALL 
SELECT [Date] +1, @Start AS StartDate FROM DateList WHERE [Date] <@End 
) 
SELECT dbo.Vacation.EmpID,DateList.[Date], dbo.Vacation.VacType FROM dbo.Vacation INNER JOIN DateList ON DateList.StartDate = dbo.Vacation.VStart

获取SQL两个日期之间的日期列表

相关推荐