SQL 加入对日期范围的查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5595409/
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
Join query on Date Range
提问by Tassadaque
HI
I have following tables
嗨，
我有以下表格
=========================
Periods
=========================
PeriodID StartDate EndDate
1 01-01-11 07-01-11
2 08-01-11 15-01-11
and so on for whole year
等等一整年
=========================
History
=========================
PersonID From To
1 01-01-11 05-04-11
2 17-06-11 NULL
and so on
I want the following output
我想要以下输出
StartDate EndDate PersonID
01-01-11 07-01-11 1
08-01-11 15-01-11 1
.
.
15-04-11 21-04-11 NULL
.
.
15-06-11 21-06-11 2
I need to take join between these two tables but i couldn't figure how join condition will be look like
我需要在这两个表之间进行连接，但我不知道连接条件会是什么样子
Ragards
拉格兹
回答by Andriy M
SELECT
p.StartDate,
p.EndDate,
h.PersonID
FROM Periods p
LEFT JOIN History h
ON h.[From] BETWEEN p.StartDate AND p.EndDate OR
p.StartDate BETWEEN h.[From] AND ISNULL(h.[To], '30000101')
回答by ysrb
Can you please try:
你能试试吗：
SELECT P.StartDate, P.EndDate, H.PersonID
FROM Period P INNER JOIN History H ON P.StartDate <= H.Fromand (P.EndDate >= H.To OR H.To IS NULL)
I have edited the SQL after reading the spec more clearly
我在更清楚地阅读规范后编辑了 SQL
I have edited the SQL again. I'm using INNER JOIN now.
我再次编辑了 SQL。我现在正在使用 INNER JOIN。
回答by MJB
It would affect performance, but I think it is worth just trying the odd looking between:
它会影响性能，但我认为值得尝试以下奇怪的外观：
select x
from table1 t1
inner join table2 t2
on t2.date between t1.startdate and t1.enddate
Whether it works or not will depend on whether this is to be production, or just a one time thing, and how many records are involved. It may be way too slow.
它是否有效将取决于这是要制作的还是一次性的，以及涉及多少记录。它可能太慢了。
回答by clyc
You need to do a left join in order to show all the periods available even if there are no history entries associated with that period. The criteria would be if the History date was between the period. You would also need to check if the To date was null and include it into your results
您需要执行左连接以显示所有可用期间，即使没有与该期间关联的历史条目。标准将是历史日期是否在期间之间。您还需要检查 To date 是否为空并将其包含在您的结果中
SELECT p.StartDate, p.EndDate, h.PersonId
FROM Period p
LEFT JOIN History h
ON p.StartDate >= h.[From] AND
(h.[To] IS NULL OR p.EndDate <= h.[To])
回答by DBDWH
at table 'history', set NULL to '9999-12-31'
在表 'history' 中，将 NULL 设置为 '9999-12-31'
select * from periods a inner join history b on a.from < b.to and a.to > b.from
select * from period a internal join history b on a.from < b.to and a.to > b.from
