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

Question

我有一个简单的配置: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 session 和 PVLDB session 上发表至少 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 倍。

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

Answer 1

如果您不关心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;