这篇文章更通用的标题是
MySql 根据另一行的值对同一表中的不同列求和,按另一行分组
我有一个员工费用表:
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 |`
我想制作这样的报告:
Employee Id | Expense Cat 1 Total Amount | Expense Cat 2 Total Amount
11 | 300 | 5
12 | 260 | 8
所以最初我认为我可以像这样对同一个表使用 2 个表别名:
SELECT
employee_id,
sum(expense_cat_1.expense_amount) as expense_1_total,
sum(expense_cat_2.expense_amount) as expense_2_total
FROM
expenses as expense_cat_1 where expense_cat_1.expense_cat_id=1 ,
expenses as expense_cat_2 where expense_cat_2.expense_cat_id=2
group by employee_id
但这不正确的 SQL 语法,这对我来说很有意义。
所以我想我可以在员工表和费用表之间做两个连接:
SELECT
employees.id as employee_id,
sum(expenses_cat_1.expense_amount) as expense_1_total,
sum(expenses_cat_2.expense_amount) as expense_2_total
FROM employees
join expenses as expenses_cat_1 on employees.id = expenses_cat_1.employee_id and expenses_cat_1.expense_cat_id=1
join expenses as expenses_cat_2 on employees.id = expenses_cat_2.employee_id and expenses_cat_2.expense_cat_id=2
group by employees.id
这很接近,但错了:
employee_id | expense_1_total | expense_2_total
11 | 300 | 10
12 | 260 | 16
因为费用 2 总计翻了一番!我认为这是因为联接为类别 1 的两项费用中的每一项显示两行,并将它们相加。
我还尝试了一种子查询方法:
SELECT (SELECT sum(expense_amount)
FROM expenses
WHERE expense_cat_id = 1) AS sum1 ,
(SELECT sum(expense_amount)
FROM expenses
WHERE expense_cat_id = 2) AS sum2,
employee_id
FROM expenses group by employee_id
但这与连接方法具有相同的问题 - Cat 2 的总数翻了一番。
如何使第二个联接仅包含一次expense_2_total???
我个人不喜欢sql案例语句,因为它们似乎更像是一种过程语言结构(sql是声明式的(,但我很高兴考虑在这种情况下使用它们 - 但我提出了SQL专家的挑战来优雅地解决这个问题。
您正在寻找条件聚合:
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;