mysql - 使用mysql上的三个表获取按类别分组的摘要

Question

category
---------------------------
id_category     primary key
category    
id_user         foreign key

counterpart
---------------------------
id_counterpart  primary key
counterpart
id_category     foreign key
id_user         foreign key

transaction
---------------------------
transaction     primary key
date
id_counterpart  foreign key
amount
id_card         foreign key
id_user         foreign key

你好，

我在 mysql 数据库上有这些表，我想根据 id_user 按月和年汇总每个类别(如果有任何交易，则为 0)。

我试过这个命令按对应方分组，它有效，但当我添加类别并按 id_category 分组时无法到达。

select counterpart, s2.total from counterpart as s1
left join (select coalesce(sum(amount),0) as total, id_counterpart from transaction where year(date) = 2019 and month(date) = 7 and id_user = 2 group by id_counterpart) as s2
on s1.id_counterpart = s2.id_counterpart
left join category on s1.id_category = category.id_category
group by counterpart;

你有什么想法吗？否则，我将使用 php。

谢谢。

---

编辑:添加示例

INSERT INTO `category` (`id_category`, `category`, `id_user`) VALUES
(1, 'cat_a',    1),
(2, 'cat_b',    1),
(3, 'cat_c',    1);

INSERT INTO `counterpart` (`id_counterpart`, `counterpart`, `id_category`, `id_user`) VALUES
(1, 'cp_a', 1,  1),
(2, 'cp_b', 2,  1),
(3, 'cp_c', 2,  1);

INSERT INTO `transaction` (`id_transaction`, `date`, `id_counterpart`, `amount`, `id_card`, `id_user`) VALUES
(1, '2019-07-01 00:00:00',  1,  400.00, 2,  1),
(2, '2019-07-01 00:00:00',  1,  -24.95, 2,  1),
(3, '2019-07-31 00:00:00',  2,  -20.04, 2,  1);
(4, '2019-07-30 00:00:00',  2,  -1.00,  2,  1);
(5, '2019-07-29 00:00:00',  3,  -2.00,  2,  1);
(6, '2019-07-28 00:00:00',  1,  -3.00,  2,  1);
(7, '2019-07-27 00:00:00',  3,  2.00,   2,  1);
(8, '2019-07-26 00:00:00',  2,  5.00,   2,  1);

2019 年 7 月，我想为用户 1 提供这个:

cat_a       372.05
cat_b       16.04
cat_c       0.00

Answer 1

加入表格，然后按类别分组:

select c.category, coalesce(sum(t.amount), 0) total 
from category c
left join counterpart as cp 
on c.id_category = cp.id_category and c.id_user = cp.id_user
left join transaction t 
on t.id_counterpart = cp.id_counterpart and t.id_user = cp.id_user and year(t.date) = 2019 and month(t.date) = 7 and t.id_user = 1
group by c.id_category, c.category

参见 demo .
结果:

| category | total  |
| -------- | ------ |
| cat_a    | 372.05 |
| cat_b    | -16.04 |
| cat_c    | 0      |