MySQL会在一周内获取所有正在工作的用户

Question

我有两个表：users和users_arrivals，我需要创建一个在特定日期范围内工作的用户列表。用户表看起来像：

| id | firstName | lastName |
| -- | --------- | -------- |
| 1  | John 1    | Test 1   |
| 2  | John 2    | Test 2   |
| 3  | John 3    | Test 3   |
| 4  | John 4    | Test 4   |

users_arrivals:

| id | user_id | start_date |  end_date  | 
| -- | ------- | ---------- | ---------- |
|  1 |       1 | 2022-09-01 | 2022-09-30 |
|  2 |       2 | 2022-09-22 | 2022-09-25 |
|  3 |       3 | 2022-09-19 | 2022-09-25 |

现在我需要获取所有在2022-09-19和2022-09-25之间工作的用户，但有些用户的范围在2022-09-01和2022-09-30之间，也应该返回。我试着让SQL变成那样，但它只返回范围小于或等于2022-09-19和2022-09-25的用户。如何获得范围更大的用户？

SELECT *
FROM users u
LEFT JOIN users_arrivals po ON po.user_id = u.id
WHERE po.start_date BETWEEN '2022-09-19' AND '2022-09-25' AND po.end_date    BETWEEN '2022-09-19' AND '2022-09-25' 
GROUP BY u.id

Answer 1

也许是这样的：

SELECT *
FROM users u
LEFT JOIN users_arrivals po ON po.user_id = u.id
WHERE '2022-09-19' BETWEEN po.start_date AND po.end_date    
OR    '2022-09-25' BETWEEN po.start_date AND po.end_date;

https://dbfiddle.uk/xf54UzPg