SQL 对于两个日期之间的每一天，在开始/结束列中添加具有相同信息但仅当天的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27489564/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
For each day between two dates, add a row with the same info but only that day in the start/end columns
提问by Justin Warner
I have a table, with types varchar, datetime, datetime:
我有一个表，类型为varchar, datetime, datetime：
NAME | START | END
Bob | 10/30 | 11/2
What's a SQL query can I look up to find out how to make that table be?:
我可以查找什么是 SQL 查询以了解如何制作该表？：
NAME | START | END
Bob | 10/30 | 10/30
Bob | 10/31 | 10/31
Bob | 11/01 | 11/01
Bob | 11/02 | 11/02
This is only ran once, and on a very small dataset. Optimization isn't necessary.
这仅在一个非常小的数据集上运行一次。优化不是必需的。
回答by P?????
May be you need a Recursive CTE.
可能你需要一个Recursive CTE.
CREATE TABLE #dates(NAME VARCHAR(50),START DATETIME,[END] DATETIME)
INSERT INTO #dates
VALUES ('Bob','2014-10-30','2014-11-02')
DECLARE @maxdate DATETIME = (SELECT Max([end]) FROM #dates);
WITH cte
AS (SELECT NAME,
START,
[END]
FROM #dates
UNION ALL
SELECT NAME,
Dateadd(day, 1, start),
Dateadd(day, 1, start)
FROM cte
WHERE start < @maxdate)
SELECT *
FROM cte
OUTPUT :
输出 ：
name START END
---- ---------- ----------
Bob 2014-10-30 2014-10-30
Bob 2014-10-31 2014-10-31
Bob 2014-11-01 2014-11-01
Bob 2014-11-02 2014-11-02
回答by Hart CO
You can do this with a recursive cte:
您可以使用递归 cte 执行此操作：
;with cte AS (SELECT Name,Start,[End]
FROM YourTable
UNION ALL
SELECT Name
,DATEADD(day,1,Start)
,[End]
FROM cte
WHERE Start < [End])
SELECT Name, Start, Start AS [End]
FROM cte
However, I suggest creating a calendar table and joining to it:
但是，我建议创建一个日历表并加入它：
SELECT a.Name,b.CalendarDate AS Start, b.CalendarDate AS [End]
FROM YourTable a
JOIN tlkp_Calendar b
ON b.CalendarDate BETWEEN a.[Start] AND a.[End]
Demo of both queries: SQL Fiddle
两个查询的演示：SQL Fiddle
回答by overflowed
;WITH dates
AS (SELECT (SELECT MIN(start) from table) as date,
UNION ALL
SELECT
Dateadd(day, 1, date),
FROM dates
WHERE date < (SELECT MAX(end) from table))
SELECT name, date as start, date as end
from table
RIGHT JOIN dates on date between start and end
回答by SherlockSpreadsheets
The problem I had with this Question/Answer is it is for one record only. I found a simple and effective solution in this answer post -- SQL how to convert row with date range to many rows with each date.
我在这个问题/答案中遇到的问题是它仅用于一个记录。我在这篇回答帖子中找到了一个简单有效的解决方案 - SQL 如何将具有日期范围的行转换为每个日期的多行。
The solution by "RichardTheKiwi" involves adding new date records based upon an Integer Table (list) and join to Source Table by evaluating the date range with datedifffunction. You can pull an integer list directly from SQL Server master database (SELECT master..spt_values WHERE v.type='P'). This
“RichardTheKiwi”的解决方案涉及基于整数表（列表）添加新的日期记录，并通过使用datediff函数评估日期范围来连接到源表。您可以直接从 SQL Server 主数据库 ( SELECT master..spt_values WHERE v.type='P') 中提取整数列表。这个
Google the search term sql spt_valuesand there are many interesting blog posts about this table. For example...
谷歌搜索词sql spt_values，有很多关于这个表的有趣的博客文章。例如...
Full Solution:
完整解决方案：
--NAME | START | END
--Bob | 10/30 | 11/2
DECLARE @SampleData as table
(PersonName nvarchar(50), StartDate date, EndDate date)
INSERT INTO @SampleData
(PersonName, StartDate, EndDate)
VALUES
('Bob', '2019-10-30', '2019-11-02')
, ('Joe', '2019-10-30', '2019-11-05')
;
WITH
cteSampleData_RecordAdded AS
-- NOTE: Range record converted to daily records for 'SampleData'
(
SELECT
T1.PersonName
, T1.StartDate
, T1.EndDate
, DATEADD(d,v.number,T1.StartDate) AS [NewRecordDate]
, DATEDIFF(day, T1.StartDate, T1.EndDate)+1 AS [QDaysActive]
FROM
@SampleData T1
-- Adds a record for each date in the range
JOIN MASTER..spt_values v
ON v.type='P'AND v.number BETWEEN 0 AND datediff(d, T1.StartDate, T1.EndDate)
)
select * from cteSampleData_RecordAdded
Results:
结果：
+------------+------------+-----------+---------------+-------------+
| PersonName | StartDate | EndDate | NewRecordDate | QDaysActive |
+------------+------------+-----------+---------------+-------------+
| Bob | 10/30/2019 | 11/2/2019 | 10/30/2019 | 4 |
+------------+------------+-----------+---------------+-------------+
| Bob | 10/30/2019 | 11/2/2019 | 10/31/2019 | 4 |
+------------+------------+-----------+---------------+-------------+
| Bob | 10/30/2019 | 11/2/2019 | 11/1/2019 | 4 |
+------------+------------+-----------+---------------+-------------+
| Bob | 10/30/2019 | 11/2/2019 | 11/2/2019 | 4 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 10/30/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 10/31/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 11/1/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 11/2/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 11/3/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 11/4/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
| Joe | 10/30/2019 | 11/5/2019 | 11/5/2019 | 7 |
+------------+------------+-----------+---------------+-------------+
