这与我之前的问题有关
Mysql Foreach 从一个表到另一个表
我设法获得了每日日志,但我现在陷入困境的是如何获取过去 5 天的每日日志,其格式与单日格式相同
SELECT a.user_id AS EmployeeId, a.username AS EmployeeName, DATE(SUBDATE(NOW(),1)) as Date, TIME_FORMAT(time(min(b.server_time)), '%r') AS 'IN', TIME_FORMAT(time(max(c.server_time)), '%r') as 'OUT'
FROM `users` a
LEFT JOIN user_attendance b ON a.user_id = b.user_id and (b.server_time BETWEEN
CONCAT(DATE(SUBDATE(NOW(),1)), ' 00:00:00') and CONCAT(DATE(SUBDATE(NOW(),1)), ' 23:59:59')) and b.action = 'IN'
LEFT JOIN user_attendance c ON a.user_id = c.user_id and (b.server_time BETWEEN CONCAT(DATE(SUBDATE(NOW(),1)), ' 00:00:00') and CONCAT(DATE(SUBDATE(NOW(),1)), ' 23:59:59')) and c.action = 'OUT'
GROUP BY a.username, a.user_id
上面的代码适用于每日日志。我需要以相同的格式获得 5 天
用户表
userID | username
01 | tiger
02 | pooh
user_attendance
user_id | dateTime | action
01 | 2019-10-01 08:00:00 | IN
01 | 2019-10-01 08:45:00 | OUT
01 | 2019-10-01 10:00:00 | IN
01 | 2019-10-01 14:00:00 | OUT
01 | 2019-10-02 08:00:00 | IN
01 | 2019-10-02 08:45:00 | OUT
01 | 2019-10-02 10:00:00 | IN
01 | 2019-10-02 14:00:00 | OUT
02 | 2019-10-01 08:00:00 | IN
02 | 2019-10-01 08:45:00 | OUT
02 | 2019-10-01 10:00:00 | IN
02 | 2019-10-01 14:00:00 | OUT
02 | 2019-10-02 08:00:00 | IN
02 | 2019-10-02 08:45:00 | OUT
02 | 2019-10-02 10:00:00 | IN
02 | 2019-10-02 14:00:00 | OUT
我期待的结果
user_id | username | IN | OUT
01 | tiger | 2019-10-01 08:00:00 | 2019-10-01 14:00:00
01 | tiger | 2019-10-02 08:00:00 | 2019-10-02 14:00:00
02 | tiger | 2019-10-01 08:00:00 | 2019-10-01 14:00:00
02 | tiger | 2019-10-02 08:00:00 | 2019-10-02 14:00:00
您似乎正在寻找条件聚合:
select
u.user_id,
u.username,
min(case when a.action = 'IN' then a.dateTime end) `in`,
max(case when a.action = 'OUT' then a.dateTime end) `out`
from
user u
inner join user_attendance a on u.user_id = a.user_id
group by
u.user_id,
u.username,
date(a.dateTime)
order by
u.user_id,
date(a.dateTime)
DB小提琴上的演示:
| user_id | username | in | out |
| ------- | -------- | ------------------- | ------------------- |
| 1 | tiger | 2019-10-01 08:00:00 | 2019-10-01 14:00:00 |
| 1 | tiger | 2019-10-02 08:00:00 | 2019-10-02 14:00:00 |
| 2 | pooh | 2019-10-01 08:00:00 | 2019-10-01 14:00:00 |
| 2 | pooh | 2019-10-02 08:00:00 | 2019-10-02 14:00:00 |
您可以根据条件添加- interval 5 day
SELECT a.user_id AS EmployeeId, a.username AS EmployeeName
, DATE(SUBDATE(NOW(),1)) as Date, TIME_FORMAT(time(min(b.server_time)), '%r') AS 'IN'
, TIME_FORMAT(time(max(c.server_time)), '%r') as 'OUT'
FROM `users` a
LEFT JOIN user_attendance b ON a.user_id = b.user_id
and (b.server_time BETWEEN CONCAT(DATE(SUBDATE(NOW(),1)), ' 00:00:00') - INTERVAL 5 DAY AND CONCAT(DATE(SUBDATE(NOW(),1)), ' 23:59:59'))
and b.action = 'IN'
LEFT JOIN user_attendance c ON a.user_id = c.user_id
and (c.server_time BETWEEN CONCAT(DATE(SUBDATE(NOW(),1)), ' 00:00:00') - INTERVAL 5 DAY AND CONCAT(DATE(SUBDATE(NOW(),1)), ' 23:59:59'))
and c.action = 'OUT'
GROUP BY a.username, a.user_id
您还可以使用curdate()
函数代替now()
来仅获取日期而不获取时间。
SELECT a.user_id AS EmployeeId, a.username AS EmployeeName
, DATE(SUBDATE(NOW(),1)) as Date, TIME_FORMAT(time(min(b.server_time)), '%r') AS 'IN'
, TIME_FORMAT(time(max(c.server_time)), '%r') as 'OUT'
FROM `users` a
LEFT JOIN user_attendance b ON a.user_id = b.user_id
and b.server_time BETWEEN curdate() - INTERVAL 5 DAY AND curdate()
and b.action = 'IN'
LEFT JOIN user_attendance c ON a.user_id = c.user_id
and c.server_time BETWEEN curdate() - INTERVAL 5 DAY AND curdate()
and c.action = 'OUT'
GROUP BY a.username, a.user_id