mysql - 从mysql查询显示今天不在场的人员列表

Question

我正在尝试显示拥有此权限的人员列表

情况1:

忘记签到而在场

情况2:

今天缺席且无法签到的人。

在这两种情况下，签到都将为空，因为都没有签到

这就是我迄今为止所取得的成就

SELECT
    u.id,
    u.`firstname` AS 'Firstname',
    u.`lastname` AS 'Lastname',
    ulr.leave_status AS 'Leave Status',
    ulr.leave_from AS 'Leave From',
    ulr.leave_to AS 'Leave To',
    ulr.leave_description AS 'Leave Description',
    DATE_FORMAT(
        ulr.leave_from,
        '%Y-%m-%d'
    ) AS 'Today''s Date'
FROM
    users u
INNER JOIN users_leave_request ulr ON u.id = ulr.user_id
INNER JOIN checkin_checkout cc ON u.id = cc.users_id
WHERE
    DATE_FORMAT(
        leave_from,
        '%Y-%m-%d'
    ) = CURDATE()
AND cc.checkin_time IS NULL

users_leave_request 表:table1

checkin_checkout 表:table2

Answer 1

此查询将选择今天未签到的所有用户，如果他们在 users_leave_request 中有记录，则也将返回该记录的数据。通过使用LEFT JOIN，我们可以避免为在users_leave_request中没有条目的未 checkin 用户返回空结果。如果我们使用JOIN，只有在users和users_leave_request表中都找到记录时才会返回结果。

SELECT
 u.id,
 u.`firstname` AS 'Firstname',
 u.`lastname` AS 'Lastname',
 ulr.leave_status AS 'Leave Status',
 ulr.leave_from AS 'Leave From',
 ulr.leave_to AS 'Leave To',
 ulr.leave_description AS 'Leave Description',
 DATE_FORMAT(
    ulr.leave_from,
    '%Y-%m-%d'
 ) AS 'Today''s Date'
FROM users AS u
LEFT JOIN users_leave_request ulr ON u.id = ulr.user_id
WHERE u.id NOT IN (
    SELECT u.id
    FROM
        users u
    INNER JOIN checkin_checkout cc ON u.id = cc.users_id
    WHERE
        DATE_FORMAT(cc.checkin_time, '%Y-%m-%d') = CURDATE()    
)