时间戳

首页 > 解决方案 > Mysql Foreach 从一张表到另一张表

mysql - Mysql Foreach 从一张表到另一张表

问题描述

我有 2 个表，第一个表是用户，第二个表是 user_attendance。

在用户考勤中，我每行插入 IN 和 OUT，因此 1 行用于 IN，1 行用于 OUT。

我想要一个 MySQL 选择（所以我不必在 Php 上做），它将循环用户中的每个 ID，并在用户表中获取该用户的最后一个 OUT 和第一个 IN。

我尝试在 Php 上执行此操作，首先选择用户中的所有 ID，然后通过 php 循环并选择该 ID 中的第一个和最后一个。像这样。

$userCreds = DB::table('users')->select('user_id', 'username')->get();

$emailBody = '<table border="1"><thead><tr><th>Employee ID</th><th>Employee Name</th><th>Date</th><th>IN</th><th>OUT</th></tr></thead><tbody>';

        foreach ($userCreds as $userCred) {
           $userAttendance = DB::select( 
                            DB::raw(
                                "SELECT username,
                                    (
                                        SELECT TIME_FORMAT(time(b.server_time), '%r')
                                        FROM `users` a
                                        LEFT JOIN user_attendance b
                                        ON a.user_id = b.user_id
                                        WHERE a.user_id = $userCred->user_id
                                        AND (b.server_time between '$ysDate' and '$yeDate')
                                        AND b.action = 'IN' ORDER BY b.server_time ASC LIMIT 1
                                    ) as TimeIn,
                                    (
                                        SELECT TIME_FORMAT(time(b.server_time), '%r')
                                        FROM `users` a
                                        LEFT JOIN user_attendance b
                                        ON a.user_id = b.user_id
                                        WHERE a.user_id = $userCred->user_id
                                        AND (b.server_time between '$ysDate' and '$yeDate')
                                        AND b.action = 'OUT' ORDER BY b.server_time DESC LIMIT 1
                                    ) as TimeOut
                                FROM users
                                WHERE user_id = $userCred->user_id"
                            )
                        ); 

            $emailBody .= '<tr><td>'.$userCred->user_id.'</td><td>'.$userCred->username.'</td><td>'.date('Y-m-d',(strtotime ( '-1 day' , strtotime ( $date) ) )).'</td><td>'.$userAttendance[0]->TimeIn.'</td><td>'.$userAttendance[0]->TimeOut.'</td></tr>';
        }

我希望在 Mysql 端完成，而不是在 Php 上完成，这样我就可以创建一个视图，该视图现在将永远存储并用于记录目的。

我需要的是这样的

用户表

用户 ID | 用户名

01 | 小猪

02 | 呸

03 | 老虎

user_attendance 表

用户 ID | 服务器时间 | 行动

01 | 2019-10-10 08:00:00 | 在

01 | 2019-10-10 09:00:00 | 出去

01 | 2019-10-10 10:00:00 | 在

01 | 2019-10-10 18:00:00 | 出去

02 | 2019-10-10 07:45:00 | 在

02 | 2019-10-10 09:00:00 | 出去

02 | 2019-10-10 10:00:00 | 在

02 | 2019-10-10 19:50:00 | 出去

结果将是

用户 ID | 日期 | 输入 | 出去

01 | 2019-20-10 | 08:00:00 | 18:00:00

02 | 2019-20-10 | 07:45:00 | 19:50:00

标签： mysqlselectforeach

解决方案

这是重构的sql查询。

首先是获取您的用户表，第二是加入IN时间，第三是加入OUT时间，第四是获取用户 ID 的时间max()和min()时间，然后按 a.user_id 分组。

SELECT a.user_id, TIME_FORMAT(time(min(b.server_time)), '%r') as TimeIn, TIME_FORMAT(time(max(c.server_time)), '%r') as TimeOut
FROM `users` a
LEFT JOIN user_attendance b ON a.user_id = b.user_id and (b.server_time between '$ysDate' and '$yeDate') and b.action = 'IN'
LEFT JOIN user_attendance c ON a.user_id = c.user_id and (c.server_time between '$ysDate' and '$yeDate') and c.action = 'OUT'
GROUP BY a.user_id

推荐阅读