Mysql Foreach从一个表到另一个表第2部分 [英] Mysql Foreach from one table to another Part 2

问题描述

这与我之前的问题有关

从一个表到另一个表的Mysql Foreach

我设法获取了日常日志，但是现在我遇到的问题是如何获取过去5天的日常日志，其格式与单日的格式相同

I managed to get the everyday logs but what I'm stuck now is how to get everyday logs for the past 5 days with the format same as the format for single 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') 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天

The code above is for daily logs. I need to get the 5 days with the same format

用户表

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

推荐答案

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

It seems like you are looking for conditional aggregation:

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

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

这篇关于Mysql Foreach从一个表到另一个表第2部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！