有趣的 SQL 连接日期之间的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7111927/
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
Interesting SQL Join on dates between dates
提问by CodingIsAwesome
First off, thanks to anyone who helps me solve this problem. I am using SQL 2005, but can use 2008 if no solution is available in 05.
首先，感谢任何帮助我解决这个问题的人。我使用的是 SQL 2005，但如果 05 中没有可用的解决方案，则可以使用 2008。
I have a rows of data that look like such:
我有一排看起来像这样的数据：
select * from mySPtable
| myPK | Area | RequestType | StartDate | EndDate |
1 SB ADD 8/14/2011 8/18/2011
2 NB RMV 8/16/2011 8/16/2011
So what I want to do is count up the total requests for each area by day. Results should be:
所以我要做的就是按天计算每个区域的总请求数。结果应该是：
| myDate | RequestType | Area | myCount |
8/14/2011 ADD SB 1
8/15/2011 ADD SB 1
8/16/2011 ADD SB 1
8/16/2011 RMV NB 1
8/17/2011 ADD SB 1
8/18/2011 ADD SB 1
How do I go about doing this? I'm stumped and no amount of googling has helped.
我该怎么做？我很难过，谷歌搜索也没有帮助。
采纳答案by Tom H
You'll need either a Calendar table or you can generate one with a CTE. Once you have that, the rest of the query should be fairly trivial. The CTE approach can be a little complex due to recursion issues and not being allowed to use aggregates, so below I've used a table variable. You can also make this a permanent table that you keep in your database.
您将需要一个日历表，或者您可以使用 CTE 生成一个。一旦你有了它，查询的其余部分应该是相当简单的。由于递归问题并且不允许使用聚合，CTE 方法可能有点复杂，所以下面我使用了一个表变量。您还可以将其设置为保存在数据库中的永久表。
SET NOCOUNT ON
DECLARE @Calendar TABLE (my_date DATETIME NOT NULL)
DECLARE @date DATETIME, @max_date DATETIME
SELECT @date = MIN(StartDate), @max_date = MAX(EndDate) FROM My_Table
WHILE (@date <= @max_date)
BEGIN
INSERT INTO @Calendar (my_date) VALUES (@date)
SELECT @date = DATEADD(dy, 1, @date)
END
SELECT
C.myDate,
M.RequestType,
M.Area,
COUNT(*) AS myCount
FROM
@Calendar C
INNER JOIN My_Table M ON
M.StartDate <= C.myDate AND
M.EndDate >= C.myDate
GROUP BY
C.myDate,
M.RequestType,
M.Area
ORDER BY
C.myDate,
M.RequestType,
M.Area
Depending on how large your potential date range is, filling the table variable could take awhile. For example, if the range spanned a decade or two.
根据您的潜在日期范围有多大，填充表变量可能需要一段时间。例如，如果范围跨越十年或两年。
回答by Clockwork-Muse
Sounds like you may want a 'Calendar' file. Especially as part of a larger business organization, this will become extremely useful.
听起来您可能想要一个“日历”文件。特别是作为大型商业组织的一部分，这将变得非常有用。
After generating the calendar, you can get your table with the following:
生成日历后，您可以使用以下内容获取表格：
SELECT a.isoDate, b.RequestType, b.Area, count(*)
FROM calendar as a
JOIN mySPTable as b
ON a.isoDate between b.StartDate and b.EndDate
WHERE a.isoDate >= [input_start_date]
AND a.isoDate < [input_end_date]
GROUP BY a.isoDate, b.RequestType, b.Area
This will generate a row for every date in the calendar file that is between the start and end dates for at least one row of mySPTable.
这将为日历文件中至少一行 mySPTable 的开始日期和结束日期之间的每个日期生成一行。
As a side note, it's also possible to generate the range of dates with a recursive CTE, but especially over the long run, I'd recommend generating and using the calendar file.
Quick CTE:
作为旁注，也可以使用递归 CTE 生成日期范围，但特别是从长远来看，我建议生成和使用日历文件。
快速 CTE：
WITH DateRange (thisDate) as (SELECT [input_start_date]
UNION ALL
SELECT DATEADD(dy, 1, thisDate)
FROM DateRange
WHERE thisDate < [input_end_date])
回答by Mikael Eriksson
You can do this with a number table (starting from 0). Here I use master..spt_values instead. SQL, Auxiliary table of numbers
您可以使用数字表（从 0 开始）执行此操作。在这里，我使用 master..spt_values 代替。SQL，辅助数字表
select dateadd(day, N.Number, M.StartDate) as myDate,
RequestType,
Area,
count(*) as myCount
from mySPtable as M
inner join master..spt_values as N
on N.Number <= datediff(day, M.StartDate, M.EndDate)
where N.type = 'P'
group by dateadd(day, N.Number, M.StartDate),
RequestType,
Area
order by dateadd(day, N.Number, M.StartDate)
