MySql 根据员工 'expense' 组中的值对 'expense type' 表中的不同类型的费用求和

Question

这篇文章的更通用的标题是

MySql Sum different columns in same table based on value of another row, group by yet another row

我有一张员工费用表:

id | employee_id | expense_cat_id | expense_amount |
 1  | 11          | 1              | 100            |
 2  | 11          | 1              | 200            |
 3  | 12          | 1              | 120            |
 4  | 12          | 1              | 140            |
 5  | 11          | 2              | 5              |
 6  | 12          | 2              | 8              |`

我想生成这样的报告:

员工 ID |费用类别 1 总额 |费用类别 2 总额
11 | 11 300 | 300 5
12 | 12 260 | 260 8

所以最初我认为我可以为同一个表使用 2 个表别名，如下所示:

选择
员工ID，
sum(expense_cat_1.expense_amount) 作为费用_1_总计，
sum(expense_cat_2.expense_amount) 作为expense_2_total
从
费用为expense_cat_1，其中expense_cat_1.expense_cat_id=1，
费用为expense_cat_2，其中expense_cat_2.expense_cat_id=2
按employee_id分组

但这不是正确的 Sql 语法，这对我来说是有意义的。

所以我想我可以在员工表和费用表之间进行两个连接:

选择
Employees.id 作为employee_id，
sum(expenses_cat_1.expense_amount) 作为费用_1_总计，
sum(expenses_cat_2.expense_amount) 作为费用_2_总计
来自员工
将费用作为费用_cat_1 加入到员工.id =费用_cat_1.employee_id 和费用_cat_1.expense_cat_id=1 上
将费用作为费用_cat_2 加入员工.id =费用_cat_2.employee_id 和费用_cat_2.expense_cat_id=2
按员工 ID 分组

这很接近，但是是错误的:

employee_id |费用_1_总计 |费用_2_总计
11 | 11 300 | 300 10
12 | 12 260 | 260 16

因为费用 2 总计增加了一倍!我认为这是因为连接为类别 1 的两项费用中的每一项显示了两行，并对它们进行了求和。

我还尝试了子查询方法:

SELECT(选择总和(费用金额)
来自费用
其中 cost_cat_id = 1) AS sum1 ，
(选择总和(费用_金额)
来自费用
其中费用_cat_id = 2) AS sum2,
员工ID
FROM 按员工 ID 分组的费用

但这与连接方法有同样的问题 - 类别 2 的总数加倍。

如何使第二次连接只包含一次费用_2_total???

我个人不喜欢 sql case 语句，因为它们看起来更像是一种过程语言构造(并且 sql 是声明性的)，但很高兴考虑它们在这种情况下的使用 - 但我向 sql 专家提出了挑战优雅地解决这个问题。

Answer 1

您正在寻找条件聚合:

SELECT employee_id,
       sum(case when expense_cat_id = 1 then expense_amount else 0 end) as expense_1_total,
       sum(case when expense_cat_id = 2 then expense_amount else 0 end) as expense_2_total
FROM expenses e
GROUP BY employee_id;