从两个具有签入签出时间的表中获取数据的SQL。

Question

我有两个桌子的员工和考勤人员

雇员表

sn   name   badge_id    user_id     employee_id
 1  user1   153      usr1          222

出席表

id  badge_id    status              timestamp
1   153    Check_out    2022-04-21 17:31:32.000000
2   153      Check_in   2022-04-21 11:31:27.000000
3   153      Check_out  2022-04-21 10:31:18.000000
4   153      Check_in   2022-04-21 08:31:13.000000 

期待得到的结果；

Name  badge_id user_id  Clock-In                    Clock-out                   HoursWorked
User1  153       usr1   2022-04-21 08:31:13.000000  2022-04-21 10:31:18.000000  02:00:05  
User1  153       usr1   2022-04-21 11:31:27.000000  2022-04-21 17:31:32.000000  06:00:05

请提出一些建议..。我可以从各个表中获得这些数据，但不能在badge_id外键上使用Join。

Answer 1

解决您的问题：

SELECT e.name, a.badge_id, e.user_id, a.timestamp check_in_at,
       MIN(b.timestamp) check_out_at, 
       TIMEDIFF(MIN(b.timestamp),a.timestamp) total_time 
FROM attendance a
INNER JOIN attendance b
ON a.timestamp < b.timestamp 
AND UPPER(a.status) = 'CHECK_IN' 
AND UPPER(b.status) = 'CHECK_OUT'
INNER JOIN employee e
ON a.badge_id = e.badge_id
GROUP BY e.name, a.badge_id, e.user_id,a.timestamp
ORDER BY a.timestamp;

另外，更新查询以获得所需的结果：

SELECT e.name, t.badge_id, e.user_id, t.timestamp check_in_at,
       t.next_TIMESTAMPDIFF check_out_at,
       TIMEDIFF(t.next_TIMESTAMPDIFF, t.timestamp ) total_time 
FROM 
(SELECT *, 
        LEAD(timestamp) OVER (PARTITION BY badge_id ORDER BY timestamp) next_TIMESTAMPDIFF 
 FROM attendance 
 WHERE timestamp BETWEEN '2022-04-21 08:00:00' AND '2022-04-21 18:00:00'
) t
INNER JOIN employee e
ON t.badge_id = e.badge_id
WHERE t.status = 'check_in' 
ORDER BY check_in_at;

检查小提琴链接中的工作示例代码和输出：

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d6ead44d3114dd918f6d6d2843ab83d2