更新时间:2023-01-24 16:33:53
问题中的解决方案"看起来根本不正确.
The "solution" in the question doesn't look right at all.
INSERT INTO table1 VALUES (5,2,'steve', '2016-04-01','2016-04-04')
现在,史蒂夫(Steve)有一个重叠的行.
Now there's a row with Steve having an overlap.
该问题中作为SOLUTION提出的查询将返回'steve'.
And the query proposed as a SOLUTION in the question will return 'steve'.
这里展示的是构建查询以返回请求的时间段内可用"用户的示例,因为表1中没有该用户的行与请求的时间段重叠".
Here's a demonstration of building a query to return the users that are "available" during the requested period, because there is no row in table1 for that user that "overlaps" with the requested period.
第一个问题是,由于存在与请求的时间段重叠的行,因此使不的用户不可用.假设表中所有行的开始日期< =结束日期...
First problem is getting the users that are not available due to the existence of a row that overlaps the requested period. Assuming that start_date <= end_date for all rows in the table...
如果行的end_date在请求的时间段的开始日期或之后,且行的start_date在请求的时间段的开始日期或之前,则行与请求的时间段重叠>所请求时段的版本.
A row overlaps the requested period, if the end_date of the row is on or after the start of the requested period, and the start_date of the row is on or before the ed of the requested period.
-- users that are "unavailable" due to row with overlap
SELECT t.user_id
FROM table1 t
WHERE t.end_date >= '2016-04-03' -- start of requested period
AND t.start_date <= '2016-04-03' -- end of requested_period
GROUP
BY t.user_id
(如果我们不假设start_date< = end_date不成立,则可以在查询中将该检查添加为条件)
(If our assumption that start_date <= end_date doesn't hold, we can add that check as a condition in the query)
要获取所有用户的列表,我们可以查询包含不同用户列表的表.我们没有在问题中看到类似的表格,因此我们可以获取出现在table1中的所有用户的列表
To get a list of all users, we could query a table that has a distinct list of users. We don't see a table like that in the question, so we can get a list of all users that appear in table1 instead
SELECT l.user_id
FROM table1 l
GROUP BY l.user_id
要获取所有用户列表(不包括不可用的用户),我们可以通过以下两种方法进行编写.最简单的是反连接模式:
To get the list of all users excluding the users that are unavailable, there are couple of ways we can write that. The simplest is an anti-join pattern:
SELECT a.user_id
FROM ( -- list of all users
SELECT l.user_id
FROM table1 l
GROUP BY l.user_id
) a
LEFT
JOIN ( -- users that are unavailable due to overlap
SELECT t.user_id
FROM table1 t
WHERE t.end_date >= '2016-04-03' -- start of requested period
AND t.start_date <= '2016-04-03' -- end of requested_period
GROUP
BY t.user_id
) u
ON u.user_id = a.user_id
WHERE u.user_id IS NULL