MySQL 将另一个查找到的值添加到 SUM 值中

Question

实际的应用程序更复杂，但这是我想要完成的简化版本。

我有一个表，其中包含每个人 (Staff_ID) 需要汇总的值 (ResponseValue)表A

+----------+-----------+---------------+
| Staff_ID | OT_Period | ResponseValue |
+----------+-----------+---------------+
| 4        | 4         | 1             |
+----------+-----------+---------------+
| 6        | 4         | 1             |
+----------+-----------+---------------+
| 8        | 4         | 1             |
+----------+-----------+---------------+
| 2        | 4         | 1             |
+----------+-----------+---------------+
| 1        | 4         | 1             |
+----------+-----------+---------------+
| 11       | 4         | 1             |
+----------+-----------+---------------+
| 13       | 4         | 0             |
+----------+-----------+---------------+
| 45       | 4         | 1             |
+----------+-----------+---------------+
| 57       | 4         | 1             |
+----------+-----------+---------------+
| 63       | 4         | 1             |
+----------+-----------+---------------+
| 1        | 4         | 1             |
+----------+-----------+---------------+
| 2        | 4         | 1             |
+----------+-----------+---------------+
| 4        | 4         | 1             |
+----------+-----------+---------------+
| 6        |           |               |
+----------+-----------+---------------+
| 8        | 4         | 1             |
+----------+-----------+---------------+
| 11       | 4         | 1             |
+----------+-----------+---------------+
| 13       | 4         | 1             |
+----------+-----------+---------------+
| 45       | 4         | 1             |
+----------+-----------+---------------+
| 57       | 4         | 1             |
+----------+-----------+---------------+
| 63       | 4         | 0             |
+----------+-----------+---------------+

我有第二个表，其中包含每个 Staff_ID 和 OT_Period 的调整值表B

+----------+-----------+------------+
| Staff_ID | OT_Period | Adjustment |
+----------+-----------+------------+
| 1        | 4         | 2          |
+----------+-----------+------------+
| 11       | 4         | 1          |
+----------+-----------+------------+
| 13       | 4         | 0          |
+----------+-----------+------------+
| 45       | 4         | 5          |
+----------+-----------+------------+
| 57       | 4         | 4          |
+----------+-----------+------------+
| 63       | 4         | 2          |
+----------+-----------+------------+

我的 MySQL 查询成功将第一个表中的值求和到名为“ShiftCount”的列中，但当我尝试向其中添加调整值时，ShiftCount 变为 Null。

预期结果是

+----------+------------+
| Staff_ID | ShiftCount |
+----------+------------+
| 1        | 4          |
+----------+------------+
| 2        | 2          |
+----------+------------+
| 4        | 2          |
+----------+------------+
| 6        | 1          |
+----------+------------+
| 8        | 2          |
+----------+------------+
| 11       | 3          |
+----------+------------+
| 13       | 1          |
+----------+------------+
| 45       | 7          |
+----------+------------+
| 57       | 6          |
+----------+------------+
| 63       | 3          |
+----------+------------+

我获取总和的工作查询是

SELECT a.Staff_ID, a.OT_PeriodID, COALESCE(SUM(ResponseValue),0) AS ShiftCount
FROM TableA a
GROUP BY a.Staff_ID

我尝试了以下操作，这使得所有“ShiftCount”值都为空

SELECT a.Staff_ID, a.OT_PeriodID, COALESCE(SUM(ResponseValue),0)+Adjustment AS ShiftCount
FROM TableA a
LEFT JOIN TableB b
ON a.Staff_ID=b.Staff_ID
GROUP BY a.Staff_ID

我错过了什么？谢谢!

Answer 1

假设每次调整都是针对 Staff_ID+OT_Period 对进行的:

SELECT a.Staff_ID, ifnull(SUM(a.ResponseValue),0)+ifnull(b.Adjustment, 0) AS ShiftCount
FROM TableA a
  LEFT JOIN TableB b ON a.Staff_ID=b.Staff_ID and a.OT_Period=b.OT_Period
GROUP BY a.Staff_ID, b.Adjustment