我需要帮助创建一个特定的查询，下面是我的存款表的一个示例，empId是一个外键，它引用了我的“users”表的主键“userId”
注意：此处不显示用户表
mysql> SELECT * FROM deposit
-> ;
+------------+---------------+---------+-------------+-------------+-------------+-------+
| CheckId | jobId | payRate | jobLocation | hours | date_paid | empId |
+------------+---------------+---------+-------------+-------------+-------------+-------+
| 1512 | entertainment | 12 | store1 | 10.00 | 2013-03-02 | 1 |
| 1510 | entertainment | 12 | store1 | 8.00 | 2013-03-01 | 1 |
| 1507 | retail | 10 | store1 | 8.00 | 2013-03-18 | 1 |
| 1506 | retail | 10 | store1 | 20.00 | 2013-03-19 | 1 |
+------------+---------------+---------+-------------+-------------+-------------+-------+
我想要的是计算所有特定jobId的所有小时的总和，在这种情况下，如果我做了
正确的查询如下所示：
+---------------+---------------+---------+
| payID | payRate | hours |
+---------------+---------------+---------+
| entertainment| 12 | 18 |
| retail | 10 | 28 |
+---------------+---------------+---------+
在这种情况下，只有两个jobid，但它可能有两个以上的jobid
这是我的查询，它只显示一个PayID，所以我需要帮助修复它。
还要注意，email是my users表的一个属性
<table>";
$query = "SELECT jobId, payRate, SUM(hours) AS 'All_Hours'
FROM users INNER JOIN deposit ON userId = empId
WHERE users.email = '" . $_SESSION['email'] ."'
GROUP BY jobId,payRate";
if (!$result) { //if the query failed
echo("Error, the query could not be executed: " .
mysqli_error($db) . "</p>");
mysqli_close($db); //close the database
} //by now we have made a successful query
while ($row = mysqli_fetch_assoc($result)){
echo "<tr><td>" .$row['jobId'] . "</td>
<td>" .$row['payRate'] . "</td>
<td>" .$row['All_Hours'] . "</td>
</tr>";
}
echo"</table>
最佳答案
您忘记在查询中添加GROUP BY子句，导致结果中只有一条记录，
SELECT jobId, payRate, SUM(hoursWorked) AS 'All_Hours'
FROM users INNER JOIN paycheck ON userId = empId
WHERE users.email = 'session_email_here'
GROUP BY jobId, payRate
关于php - 使用mysql和php创建特定的查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15514644/