我有两个表要加入,1 个是用户,1 个是出勤。
TABLE : attendance
id userId totalHours
1 1 0745
2 3 0845
3 1 0945
TABLE : user
id name departmentId
1 John 2
2 Sean 2
3 Allan 2
并非每个用户都有出勤记录(总时数)
但我需要按 userId WHERE DepartmentId = XXXX 进行查询,并对存在的每个总时间进行求和,而不会忽略没有任何出勤记录的 userId。
到目前为止我做了这个:
$result = mysqli_query($con,"SELECT * FROM user WHERE departmentId = 2");
while($row = mysqli_fetch_array($result))
{
$id = $row['userId'];
$result2 = mysqli_query($con,"SELECT * FROM attendance WHERE userId = $id");
while($row2 = mysqli_fetch_array($result2))
$totalHours = 0;
{
$totalHours = $row2['totalHours'];
$grandTotal += $totalHours;
$totalHoursInHHmm = substr_replace($totalHours,":",2,0);
$parsed = date_parse($totalHoursInHHmm);
$toSeconds = $parsed['hour'] * 3600 + $parsed['minute'] * 60;
$total += $toSeconds;
$init = $total;
$hours = floor($init / 3600);
$minutes = floor(($init / 60) % 60);
}
echo "$hours:$minutes";
}
结果显示了部门中的所有用户,并对每个 userId 的所有总小时数进行了 SUM,但错误是,有没有任何出勤的 userId 仍然显示 SUM 值,继承了之前的总 Sum
任何帮助表示赞赏:)
我需要按 userId WHERE DepartmentId = XXXX 和 SUM 每个进行查询
他们存在的totalHours,不忽略没有任何的userId
记录出席情况。
显示给定部门中所有用户的工作时间,甚至是在部门中没有行的用户attendance
表,使用LEFT JOIN
Use (CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100)
将 varchar 小时+分钟转换为单个小时数。
$query = "SELECT u.id,
SUM((CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100)) grandTotal
FROM user u
LEFT JOIN attendance a
ON u.id = a.userId
WHERE u.departmentId = 2
GROUP BY u.id";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result)) {
print $row['id'] . ' ' . $row['grandTotal'];
}
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)