mysql - 使用左连接获取记录

Question

这个问题已经有答案了:

Return default result for IN value regardless (2 个回答)

已关闭 4 年前。

我想要用户表的所有记录，但查询仅返回两个表中匹配的记录。无论用户出勤是否在出勤表中，我都会显示当月的用户出勤情况。

SELECT MAX(a1.attendance_id) atid,
a1.attendance_user_id, 
CONCAT(u1.user_firstname, ' ', u1.user_lastname) user_name, 
YEAR(attendance_date) at_year, 
MONTH(attendance_date) at_month, 
GROUP_CONCAT(DAY(attendance_date ) ORDER BY attendance_date) at_date, 
GROUP_CONCAT(attendance_type ORDER BY attendance_date) at_type 
FROM user u1 LEFT JOIN attendance a1 ON u1.user_id = a1.attendance_user_id 
WHERE u1.user_status = 'Active' AND DATE(a1.attendance_date) 
BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() 
GROUP BY a1.attendance_user_id, YEAR(attendance_date), MONTH(attendance_date) ORDER BY atid DESC limit 0, 25

Answer 1

您需要将 DATE(a1.attendance_date) BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() 条件放入 ON 子句

SELECT 
    MAX(a1.attendance_id) atid,
    a1.attendance_user_id, 
    CONCAT(u1.user_firstname, ' ', u1.user_lastname) user_name, 
    YEAR(attendance_date) at_year, 
    MONTH(attendance_date) at_month, 
    GROUP_CONCAT(DAY(attendance_date ) ORDER BY attendance_date) at_date, 
    GROUP_CONCAT(attendance_type ORDER BY attendance_date) at_type 
FROM user u1 LEFT JOIN attendance a1 ON u1.user_id = a1.attendance_user_id 
AND DATE(a1.attendance_date) BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-01') AND CURDATE() 
WHERE u1.user_status = 'Active' 
GROUP BY a1.attendance_user_id, YEAR(attendance_date), MONTH(attendance_date) 
ORDER BY atid DESC limit 0, 25