MySQL:内连接时出现重复行

Question

我是这个网站的新人。我正在尝试为用 PHP 和 MySQL 创建的考勤系统找到有关此重复行问题的解决方案。

在该系统中，每个用户可以在每个工作日执行 checkin 和 checkout 。有些用户可以在一天内进行多次 checkin 和 checkout 以进行轮类工作，忽略 LATE 状态( checkin 时上午 9.05 点之后)和 EARLY 状态( checkout 时下午 5.00 点之前)。该系统的MySQL数据库中有三个表。

tbl_checkin，用于存储 checkin 数据，数据库中有以下内容:

ID     User_Id     Date          Time     Late_status          Comment
------------------------------------------------------------------------------------------
1     UGT001     2017-12-29     08:14:13                   No Comment
2     UGT002     2017-12-29     09:54:52     LATE          Comment Goes Here
3     UGT001     2017-12-30     09:16:34     LATE          Sending kids to school
4     UGT002     2017-12-30     08:21:04               
5     UGT003     2018-01-02     08:05:11                   
6     UGT003     2018-01-02     12:05:32                   Second Checkin

tbl_checkout，用于存储结账数据:

ID     User_Id     Date          Time     Early_status          Comment
------------------------------------------------------------------------------------------
1     UGT001     2017-12-29     17:14:13                   
2     UGT002     2017-12-29     15:54:13     EARLY         Hospital Appointment
3     UGT001     2017-12-30     16:58:21     EARLY         Late for movie night
4     UGT002     2017-12-30     18:00:43               
5     UGT003     2018-01-02     10:30:44                   Break Time
6     UGT003     2018-01-02     18:04:45                   

tbl_userlogin，用于存储用户数据(以及此处未显示的其他信息，例如工作开始日期、结束日期、密码、持续时间、角色和用户状态):

ID     User_Id     Name          
------------------------------------------------------------------------------------------
1     UGT001     KEVIN MARTIN                 
2     UGT002     AHMAD BIN IBRAHIM  
3     UGT003     ALISYA PUTRI KARISMA

对于这个问题，我使用这个SQL查询来显示完整的考勤日志，显示用户名、用户ID、日期、签到时间、 checkout 时间、 checkin 评论和 checkout 评论，对最新条目进行排序(按日期)首先:

SELECT DISTINCT 
    b.name AS Name, 
    c.user_id AS User_ID , 
    c.date AS Date, 
    a.time As "Time In",
    c.time As "Time Out",
    a.comment AS "Comment In",
    c.comment AS "Comment Out" 
FROM 
tbl_checkout c 
LEFT JOIN tbl_checkin a ON c.date=a.date AND c.user_id=a.user_id 
INNER JOIN tbl_userlogin b ON c.user_id=b.user_id 
ORDER BY c.date DESC

这是我应该显示的结果:

Name                    User_ID     Date         Time In     Time Out     Comment In               Comment Out
----------------------------------------------------------------------------------------------------------------------
ALISYA PUTRI KARISMA    UGT003    2018-01-02    12:05:32     18:04:45    Second Checkin           
ALISYA PUTRI KARISMA    UGT003    2018-01-02    08:05:11     10:30:44                            Break Time
KEVIN MARTIN            UGT001    2017-12-30    09:16:34     16:58:21    Sending kids to school   Late for movie night
AHMAD BIN IBRAHIM       UGT002    2017-12-30    08:21:04     18:00:43
AHMAD BIN IBRAHIM       UGT002    2017-12-29    09:54:52     15:54:13    Comment Goes Here        Hospital Appointment
KEVIN MARTIN            UGT001    2017-12-29    08:14:13     17:14:13    No Comment

但是，执行此 SQL 查询将显示如下:

Name                    User_ID     Date         Time In     Time Out     Comment In               Comment Out
----------------------------------------------------------------------------------------------------------------------
ALISYA PUTRI KARISMA    UGT003    2018-01-02    12:05:32     10:30:44    Second Checkin           Break Time
ALISYA PUTRI KARISMA    UGT003    2018-01-02    08:05:11     18:04:45    
ALISYA PUTRI KARISMA    UGT003    2018-01-02    12:05:32     18:04:45    Second Checkin           
ALISYA PUTRI KARISMA    UGT003    2018-01-02    08:05:11     10:30:44                            Break Time
KEVIN MARTIN            UGT001    2017-12-30    09:16:34     16:58:21    Sending kids to school   Late for movie night
AHMAD BIN IBRAHIM       UGT002    2017-12-30    08:21:04     18:00:43
AHMAD BIN IBRAHIM       UGT002    2017-12-29    09:54:52     15:54:13    Comment Goes Here        Hospital Appointment
KEVIN MARTIN            UGT001    2017-12-29    08:14:13     17:14:13    No Comment

从上面的结果来看UGT003(该用户可以执行多次 checkin 和 checkout /轮类安排)，由于读取了不同的 checkin 时间/评论和 checkout 时间/评论而重复了。

有人可以帮我解决我的考勤系统的这个问题吗？谢谢。

Answer 1

问题是您仅使用日期来加入 checkin 和 checkout ，如果一天内有多个 checkin 和 checkout ，则不足以匹配它们。它将当天的每个in 与每个out 进行匹配，这会导致重复(甚至当同一日期添加更多案例时会出现乘法)。

尝试这个查询:

SELECT
    b.name AS Name, 
    c.user_id AS User_ID , 
    c.date AS Date, 
    a.time As "Time In",
    c.time As "Time Out",
    a.comment AS "Comment In",
    c.comment AS "Comment Out"
FROM 
tbl_checkin a
LEFT JOIN tbl_checkout c ON c.date=a.date AND c.user_id=a.user_id AND c.Time >= a.Time
LEFT JOIN tbl_checkout co2 ON co2.user_id=c.user_id AND c.date=co2.date  AND co2.Time >= a.Time AND co2.Time < c.Time
INNER JOIN tbl_userlogin b ON c.user_id=b.user_id 


WHERE 
    co2.ID IS NULL

ORDER BY c.date DESC, a.Time, c.Time