MySQL MySQL查询以选择开始/结束日期之间的事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17014066/
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
MySQL query to select events between start/end date
提问by Guicara
I have a MySQL table named 'events' that contains event data. The important columns are 'start' and 'end' which contain string (YYYY-MM-DD) to represent when the events starts and ends.
我有一个名为“events”的 MySQL 表，其中包含事件数据。重要的列是“开始”和“结束”，它们包含字符串 (YYYY-MM-DD) 来表示事件开始和结束的时间。
I want to get the records for all the active events in a time period.
我想获取一段时间内所有活动事件的记录。
Events:
事件：
------------------------------ ID | START | END | ------------------------------ 1 | 2013-06-14 | 2013-06-14 | 2 | 2013-06-15 | 2013-08-21 | 3 | 2013-06-22 | 2013-06-25 | 4 | 2013-07-01 | 2013-07-10 | 5 | 2013-07-30 | 2013-07-31 | ------------------------------
Request/search:
请求/搜索：
Example: All events between 2013-06-13 and 2013-07-22 : #1, #3, #4 SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22' : #1, #2, #3, #4 SELECT id FROM events WHERE end BETWEEN '2013-06-13' AND '2013-07-22' : #1, #3, #4 ====> intersect : #1, #3, #4
Example: All events between 2013-06-14 and 2013-06-14 : SELECT id FROM events WHERE start BETWEEN '2013-06-14' AND '2013-06-14' : #1 SELECT id FROM events WHERE end BETWEEN '2013-06-14' AND '2013-06-14' : #1 ====> intersect : #1
I tried many queries still I fail to get the exact SQL query.
我尝试了很多查询，但仍然无法获得确切的 SQL 查询。
Don't you know how to do that? Any suggestions?
你不知道怎么做吗？有什么建议？
Thanks!
谢谢！
回答by Fabio
If I understood correctly you are trying to use a single query, i think you can just merge your date search toghter in WHEREclauses
如果我理解正确，您正在尝试使用单个查询，我认为您可以将您的日期搜索合并到WHERE子句中
SELECT id
FROM events
WHERE start BETWEEN '2013-06-13' AND '2013-07-22'
AND end BETWEEN '2013-06-13' AND '2013-07-22'
or even more simply you can just use both column to set search time filter
或者更简单地说，您可以只使用两列来设置搜索时间过滤器
SELECT id
FROM events
WHERE start >= '2013-07-22' AND end <= '2013-06-13'
回答by Peter de Groot
You need the events that start and end within the scope. But that's not all: you also want the events that start within the scope and the events that end within the scope. But then you're still not there because you also want the events that start before the scope and end after the scope.
您需要在范围内开始和结束的事件。但这还不是全部：您还需要在范围内开始的事件和在范围内结束的事件。但是您仍然不在那里，因为您还希望在范围之前开始并在范围之后结束的事件。
Simplified:
简化：
- events with a start date in the scope
- events with an end date in the scope
- events with the scope startdate between the startdate and enddate
- 范围内具有开始日期的事件
- 范围内具有结束日期的事件
- startdate 和 enddate 之间范围为 startdate 的事件
Because point 2 results in records that also meet the query in point 3 we will only need points 1 and 3
因为第 2 点产生的记录也满足第 3 点中的查询，所以我们只需要第 1 点和第 3 点
So the SQL becomes:
所以 SQL 变成了：
SELECT * FROM events
WHERE start BETWEEN '2014-09-01' AND '2014-10-13'
OR '2014-09-01' BETWEEN start AND end
回答by Saurabh Chandra Patel
Here lot of good answer but i think this will help someone
这里有很多很好的答案，但我认为这会帮助某人
select id from campaign where ( NOW() BETWEEN start_date AND end_date)
回答by Olivier Coilland
SELECT id
FROM events
WHERE start <= '2013-07-22'
AND end >= '2013-06-13';
Or use MIN()and MAX()if you don't know the precedence.
或者使用MIN()andMAX()如果您不知道优先级。
回答by Salman A
I am assuming that active events in a time periodmeans at least one day of the event falls inside the time period. This is a simple "find overlapping dates" problem and there is a generic solution:
我假设某个时间段内的活动事件意味着该事件至少有一天在该时间段内。这是一个简单的“查找重叠日期”问题，并且有一个通用的解决方案：
-- [@d1, @d2] is the date range to check against
SELECT * FROM events WHERE @d2 >= start AND end >= @d1
Some tests:
一些测试：
-- list of events
SELECT * FROM events;
+------+------------+------------+
| id | start | end |
+------+------------+------------+
| 1 | 2013-06-14 | 2013-06-14 |
| 2 | 2013-06-15 | 2013-08-21 |
| 3 | 2013-06-22 | 2013-06-25 |
| 4 | 2013-07-01 | 2013-07-10 |
| 5 | 2013-07-30 | 2013-07-31 |
+------+------------+------------+
-- events between [2013-06-01, 2013-06-15]
SELECT * FROM events WHERE '2013-06-15' >= start AND end >= '2013-06-01';
+------+------------+------------+
| id | start | end |
+------+------------+------------+
| 1 | 2013-06-14 | 2013-06-14 |
| 2 | 2013-06-15 | 2013-08-21 |
+------+------------+------------+
-- events between [2013-06-16, 2013-06-30]
SELECT * FROM events WHERE '2013-06-30' >= start AND end >= '2013-06-16';
+------+------------+------------+
| id | start | end |
+------+------------+------------+
| 2 | 2013-06-15 | 2013-08-21 |
| 3 | 2013-06-22 | 2013-06-25 |
+------+------------+------------+
-- events between [2013-07-01, 2013-07-01]
SELECT * FROM events WHERE '2013-07-01' >= start AND end >= '2013-07-01';
+------+------------+------------+
| id | start | end |
+------+------------+------------+
| 2 | 2013-06-15 | 2013-08-21 |
| 4 | 2013-07-01 | 2013-07-10 |
+------+------------+------------+
-- events between [2013-07-11, 2013-07-29]
SELECT * FROM events WHERE '2013-07-29' >= start AND end >= '2013-07-11';
+------+------------+------------+
| id | start | end |
+------+------------+------------+
| 2 | 2013-06-15 | 2013-08-21 |
+------+------------+------------+
回答by Philip Sheard
SELECT *
FROM events
WHERE start <= '2013-07-22' OR end >= '2013-06-13'
回答by echo_Me
回答by Lord Bobbymort
EDIT: I've squeezed the filter a lot. I couldn't wrap my head around it before how to make sure something really fit within the time period. It's this: Start date BEFORE the END of the time period, and End date AFTER the BEGINNING of the time period
编辑：我已经挤压了很多过滤器。在如何确保某些东西在这段时间内真正适合之前，我无法理解它。是这样的：开始日期 BEFORE END 时间段，和结束日期 AFTER 时间段 BEGINNING
With the help of someone in my office I think we've figured out how to include everyone in the filter. There are 5 scenarios where a student would be deemed active during the time period in question:
在我办公室某人的帮助下，我想我们已经找到了如何将每个人都包含在过滤器中的方法。在 5 种情况下，学生在相关时间段内会被视为活跃：
1) Student started and ended during the time period.
1) 学生在该时间段内开始和结束。
2) Student started before and ended during the time period.
2) 学生在该时间段之前开始和结束。
3) Student started before and ended after the time period.
3) 学生在时间段之前开始并在时间段之后结束。
4) Student started during the time period and ended after the time period.
4) 学生在该时间段内开始并在该时间段之后结束。
5) Student started during the time period and is still active (Doesn't have an end date yet)
5) 学生在该时间段内开始并仍然活跃（还没有结束日期）
Given these criteria, we can actually condense the statements into a few groups because a student can only end between the period dates, after the period date, or they don't have an end date:
考虑到这些标准，我们实际上可以将语句压缩为几组，因为学生只能在期间日期之间、期间日期之后结束，或者他们没有结束日期：
1) Student ends during the time period AND [Student starts before OR during]
1) 学生在时间段内结束 AND [学生在 OR 期间之前开始]
2) Student ends after the time period AND [Student starts before OR during]
2) 学生在时间段之后结束 AND [学生在 OR 期间之前开始]
3) Student hasn't finished yet AND [Student starts before OR during]
3) 学生尚未完成 AND [学生开始之前或期间]
(
(
student_programs.END_DATE >= '07/01/2017 00:0:0'
OR
student_programs.END_DATE Is Null
)
AND
student_programs.START_DATE <= '06/30/2018 23:59:59'
)
I think this finally covers all the bases and includes all scenarios where a student, or event, or anything is active during a time period when you only have start date and end date. Please, do not hesitate to tell me that I am missing something. I want this to be perfect so others can use this, as I don't believe the other answers have gotten everything right yet.
我认为这最终涵盖了所有基础，包括学生、活动或任何活动在您只有开始日期和结束日期的时间段内处于活动状态的所有场景。请不要犹豫，告诉我我遗漏了什么。我希望这是完美的，以便其他人可以使用它，因为我不相信其他答案已经把一切都做好了。
回答by bkm
If you would like to use INTERSECT option, the SQL is as follows
如果你想使用INTERSECT选项，SQL如下
(SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22')
INTERSECT
(SELECT id FROM events WHERE end BETWEEN '2013-06-13' AND '2013-07-22')
