Mysql Foreach从一个表到另一个 [英] Mysql Foreach from one table to another
问题描述
我有2个表，第一个表是user，第二个表是user_attendance.
I have 2 tables, First table is user and second table is user_attendance.
在用户出席人数中，我每行插入IN和OUT，因此IN插入1行，OUT插入1行.
In the user attendance I'm inserting IN and OUT per line, so 1 line for IN and 1 line for OUT.
我想要一个MySQL选择(因此不必在Php上执行)，它将为用户中的每个ID循环并在用户表中获取该用户的最后一个OUT和第一个IN.
I want a MySQL select(so I don't have to do it on Php) which will loop for every ID in users and get the last OUT and first IN of that user in user table.
我尝试在Php上进行操作，方法是首先选择用户中的所有ID，然后通过php使其循环，然后从出勤中选择该ID中的第一个和最后一个.像这样.
I tried doing it on Php by selecting first all the ID in user and looping it Via php and Select the 1st in and Last out of that Id in attendance. Like so.
$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>';
}
我希望它不是在Php上完成，而是希望在Mysql上完成，因此我实际上可以创建一个视图，该视图现在将被存储并完成以用于记录.
Instead of doing it on Php, I want it to be done on the Mysql side so I can actually create a view which will be stored ever now and done for recording purposes.
我需要的是这样
用户表
user_id |用户名
user_id | username
01 |仔猪
02 |小熊维尼
03 |老虎
user_attendance表
user_id |服务器时间|行动
user_id | servertime | action
01 | 2019-10-10 08:00:00 | IN
01 | 2019-10-10 08:00:00 | IN
01 | 2019-10-10 09:00:00 |出
01 | 2019-10-10 09:00:00 | OUT
01 | 2019-10-10 10:00:00 | IN
01 | 2019-10-10 10:00:00 | IN
01 | 2019-10-10 18:00:00 |出
01 | 2019-10-10 18:00:00 | OUT
02 | 2019-10-10 07:45:00 | IN
02 | 2019-10-10 07:45:00 | IN
02 | 2019-10-10 09:00:00 |出
02 | 2019-10-10 09:00:00 | OUT
02 | 2019-10-10 10:00:00 | IN
02 | 2019-10-10 10:00:00 | IN
02 | 2019-10-10 19:50:00 |出
02 | 2019-10-10 19:50:00 | OUT
结果将是
user_id |日期| IN |出
user_id | date | IN | OUT
01 | 2019-20-10 | 08:00:00 | 18:00:00
01 | 2019-20-10 | 08:00:00 | 18:00:00
02 | 2019-20-10 | 07:45:00 | 19:50:00
02 | 2019-20-10 | 07:45:00 | 19:50:00
推荐答案
以下是重构的sql查询.
首先是获取您的用户表
第二个是加入IN的时间
第三是加入OUT时间
第四个是获取用户ID的max()和min()时间，然后按a.user_id分组.
first is to get your users table
second is the join the IN time
third is to join the OUT time
fourth is get the max() and min() time of user id then group by 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
这篇关于Mysql Foreach从一个表到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
