mysql - 具有多个参数的GROUP BY和COUNT

我有一个简单的配置：
2个表以多对多关系链接，因此它给了我3个表。

表作者：

idAuthor INT
name VARCHAR

表发布：

idPublication INT,
title VARCHAR,
date YEAR,
type VARCHAR,
conference VARCHAR,
journal VARCHAR

表author_has_publication：

Author_idAuthor,
Publication_idPublication

我试图让所有在SIGMOD会议和PVLDB会议上发表过至少2篇论文的作者都成为名字。
现在，我实现了这一目标，但我仍然得到了双重结果。我的查询：

SELECT author.name, publication.journal, COUNT(*)
FROM author
INNER JOIN author_has_publication
    ON author.idAuthor = author_has_publication.Author_idAuthor
INNER JOIN publication
    ON author_has_publication.Publication_idPublication = publication.idPublication
GROUP BY publication.journal, author.name
HAVING COUNT(*) >= 2
   AND (publication.journal = 'PVLDB' OR  publication.journal = 'SIGMOD');

退货

+-------+---------+----------+
| name  | journal | COUNT(*) |
+-------+---------+----------+
| Renee | PVLDB   |        2 |
| Renee | SIGMOD  |        2 |
+-------+---------+----------+

如您所见，结果是正确的，但增加了一倍，因为我只想要名称的1倍。

另一个问题，如何只为一个会议修改number参数，例如让所有发表至少3 SIGMOD和至少1 PVLDB的作者？

最佳答案

如果您不关心journal，请不要选择它，它会拆分您的结果。另外，普通的过滤器需要放在WHERE子句中，而不是HAVING子句中：

SELECT author.name, COUNT(*)
  FROM author
 INNER JOIN author_has_publication
    ON author.idAuthor = author_has_publication.Author_idAuthor
 INNER JOIN publication
    ON author_has_publication.Publication_idPublication =
       publication.idPublication
WHERE  publication.journal IN('PVLDB','SIGMOD')
 GROUP BY author.name
HAVING COUNT(CASE WHEN publication.journal = 'SIGMOD' THEN 1 END) >= 2
   AND COUNT(CASE WHEN publication.journal = 'PVLDB' THEN 1 END) >= 2;

对于第二个问题，请使用以下HAVING()子句：

HAVING COUNT(CASE WHEN publication.journal = 'SIGMOD' THEN 1 END) >= 3
   AND COUNT(CASE WHEN publication.journal = 'PVLDB' THEN 1 END) >= 1;