MySQL:一列的总和基于另一列的值

Question

如果另一列的值是这个/那个，我想要一个列的总和。这是我的表结构

id  leave_hours leave_type overtime_hours employee_id
1     7.6          1           0               3
2      5           2           0               3
3      0           0           2.3             3

我已经有了这个查询，但我需要有一个列，该列仅在休假类型为 1 且休假类型为 2 时求和

SELECT employee_id, SUM(overtime_hours) AS ot_hrs, SUM(leave_hours if leave_type == 1) AS  
       leave_1, SUM(leave_hours if leave_type == 2) AS leave_2
FROM table
GROUP BY employee_id

结果应该是:

employee_id  ot_hrs leave_1  leave_2
    3          2.3    7.6       5

感谢您的投入。

Answer 1

你需要使用条件聚合来分别对不同的leave_hours求和:

SELECT employee_id, 
       SUM(overtime_hours) AS ot, 
       SUM(CASE WHEN leave_type = 1 THEN leave_hours ELSE 0 END) AS leave_1, 
       SUM(CASE WHEN leave_type = 2 THEN leave_hours ELSE 0 END) AS leave_2
FROM `table`
GROUP BY employee_id

输出:

employee_id ot      leave_1     leave_2
3           2.3     7.6         5

Demo on dbfiddle

请注意，如果您使用 float 来存储小时数，您可能需要对结果进行ROUND。