php - 使用 mysql 和 php 创建特定查询

Question

我需要帮助创建一个特定的查询，下面是我的存款表的一个例子，empId 是一个外键，它指的是我的“用户”表的主键是“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 | 
+---------------+---------------+---------+

在这种情况下只有两个 jobIds 但它可以有超过 2 个

这是我的查询，它只显示一个 payId，所以我需要帮助修复它

另请注意，电子邮件是我的用户表的一个属性

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

Answer 1

您忘记在查询中添加 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