MySQL 从一个表中选择特定日期的行，而不是在另一个表中选择行

Question

我有两个餐 table 人员和员工登录时间，如下所述。我想提取那些未在特定日期登录的员工。

individuals
+----+------+--------------+
| id | name | phone_number |
+----+------+--------------+
| 1  | Smith | 1234567897 |
+----+------+--------------+
| 2  | David | 9874561237 |
+----+------+--------------+
| 3  | John | 7778889991 |
+----+------+--------------+

staff_login_times
+----+------+--------------+
| id | individual_id | login_date |
+----+------+--------------+
| 1  | 2             | 2017-02-27 |
+----+------+--------------+
| 2  | 1             | 2017-02-26 |
+----+------+--------------+
| 3  | 2             | 2017-02-26 |
+----+------+--------------+
| 4  | 3             | 2017-02-26 |
+----+------+--------------+

如何查找 27 日未出席的人员

Result
+----+------+--------------+
| id | name  | phone_number |
+----+------+--------------+
| 1  | Smith | 1234567897   |
+----+------+--------------+
| 3  | John  | 7778889991   |
+----+------+--------------+

我尝试过的查询:

SELECT *
FROM individuals
LEFT JOIN staff_login_times ON individuals.id = staff_login_times.individual_id
WHERE staff_login_times.individual_id IS NULL
  AND staff_login_times.login_date = '2017-02-27'

Answer 1

只是小小的改变，将你的 and 条件与 join 语句一起使用:-

SELECT *
FROM individuals
LEFT JOIN staff_login_times ON individuals.id = staff_login_times.individual_id AND staff_login_times.login_date = '2017-02-27'
WHERE staff_login_times.individual_id IS NULL