mysql - 按各种类型分组

Question

假设我有一些文章可以被用户或员工评论:

create table article (id int, title varchar(30));
create table type (id int, name varchar(30));
create table comment (id int, content varchar(30), article_id int, type_id int);
insert into article (id, title) values (1, 'article1'), (2, 'article2'), (3, 'article3'), (4, 'article4');
insert into type (id, name) values (1, 'User comment'), (2, 'Staff comment');
insert into comment (id, content, article_id, type_id) values (1, 'comment1', 1, 1), (2, 'comment2', 1, 2), (3, 'comment3', 2, 1), (4, 'comment4', 3, 2);

然后我可以计算每种评论类型的数量:

select a.id, a.title,
case when c.type_id = 1 then count(a.id) else 0 end as usercommentcount,
case when c.type_id = 2 then count(a.id) else 0 end as staffcommentcount
from article a left join comment c on a.id = c.article_id
group by a.id, c.type_id;

id title    usercommentcount staffcommentcount
1  article1 1                0
1  article1 0                1
2  article2 1                0
3  article3 0                1
4  article4 0                0

但是，由于我使用的是 Doctrine，所以我只想按 article.id 进行分组，但这会将 article1 算作有 2 条用户评论和 0 条员工评论:

select a.id, a.title,
case when c.type_id = 1 then count(a.id) else 0 end as usercommentcount,
case when c.type_id = 2 then count(a.id) else 0 end as staffcommentcount
from article a left join comment c on a.id = c.article_id
group by a.id;

id title    usercommentcount staffcommentcount
1  article1 2                0
2  article2 1                0
3  article3 0                1
4  article4 0                0

有没有办法解决这个问题，可以与 Doctrine QueryBuilder 一起使用，即不使用汇总等？理想情况下，我想要这样的结果:

id title    usercommentcount staffcommentcount
1  article1 1                1
2  article2 1                0
3  article3 0                1
4  article4 0                0

Answer 1

似乎 sum(case ... end) 在这里完成了工作:

select a.id, a.title,
sum(case when c.type_id = 1 then 1 else 0 end) as usercommentcount,
sum(case when c.type_id = 2 then 1 else 0 end) as staffcommentcount
from article a
left join comment c on a.id = c.article_id
group by a.id;