假设我的文章可以由用户或员工发表评论：

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

但是，因为我使用的是条令，所以我只想按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

有没有一种方法可以解决这个问题，即不使用汇总等。？理想情况下，我想要这样的结果：

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