php - 如何使用mysql php显示缺席和存在

Question

如何显示缺席和在场此代码仅显示当前，但我也希望缺少结果和日期如果我删除 WHERE a.date BETWEEN '2015-05-20' AND '2015-05-20'

这个然后缺席和现在的结果显示完美，但我想要选定的日期

如何解决这个问题请帮我解决这个问题

谢谢

现场示例请点击此链接

http://sqlfiddle.com/#!9/9604f/2

Mysql代码

SELECT s.user_name, s.user_pass,a.date
    ,  CASE WHEN a.user_name IS NOT NULL THEN 'Present' ELSE 'Absent' END as attendance_status
    FROM users s 
    LEFT JOIN attend a on s.user_name=a.user_name WHERE a.date BETWEEN '2015-05-20' AND '2015-05-20'

Answer 1

您需要将条件从 where 移动到 on 子句:

SELECT s.user_name, s.user_pass, a.date,
       (CASE WHEN a.user_name IS NOT NULL THEN 'Present' ELSE 'Absent'
        END) as attendance_status
FROM users s LEFT JOIN
     attend a
     ON s.user_name = a.user_name AND a.date BETWEEN '2015-05-20' AND '2015-05-20';

WHERE 子句将外连接转换为内连接，因为它过滤掉了 NULL 值。