mysql - Mysql Foreach 从一个表到另一个表第 2 部分

Question

这与我之前的问题有关

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_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

标签： mysqldatedatetimejoinselect

Answer 1

似乎您正在寻找条件聚合：

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 Fiddle 上的演示：

| 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 |