mysql - Mysql 中 count() 的不等式

Question

我有以下结构:

Table Author :
idAuthor,
Name 

+----------+-------+
| idAuthor | Name  |
+----------+-------+
|        1 | Renee |
|        2 | John  |
|        3 | Bob   |
|        4 | Bryan |
+----------+-------+
Table Publication:
idPublication,
Title,
Type,
Date,
Journal,
Conference
+---------------+--------------+------+-------------+------------+-----------+
| idPublication | Title        | Date | Type        | Conference | Journal   |
+---------------+--------------+------+-------------+------------+-----------+
|             1 | Flower thing | 2008 | book        | NULL       | NULL      |
|             2 | Bees         | 2009 | article     | NULL       | Le Monde  |
|             3 | Wasps        | 2010 | inproceding | KDD        | NULL      |
|             4 | Whales       | 2010 | inproceding | DPC        | NULL      |
|             5 | Lyon         | 2011 | article     | NULL       | Le Figaro |
|             6 | Plants       | 2012 | book        | NULL       | NULL      |
|             7 | Walls        | 2009 | proceeding  | KDD        | NULL      |
|             8 | Juices       | 2010 | proceeding  | KDD        | NULL      |
|             9 | Fruits       | 2010 | proceeding  | DPC        | NULL      |
|            10 | Computers    | 2010 | inproceding | DPC        | NULL      |
|            11 | Phones       | 2010 | inproceding | DPC        | NULL      |
|            12 | Creams       | 2010 | proceeding  | DPC        | NULL      |
|            13 | Love         | 2010 | proceeding  | DPC        | NULL      |
+---------------+--------------+------+-------------+------------+-----------+

Table author_has_publication :
Author_idAuthor,
Publication_idPublication
+-----------------+---------------------------+
| Author_idAuthor | Publication_idPublication |
+-----------------+---------------------------+
|               1 |                         1 |
|               2 |                         2 |
|               3 |                         3 |
|               4 |                         4 |
|               1 |                         5 |
|               2 |                         5 |
|               3 |                         5 |
|               3 |                         6 |
|               4 |                         7 |
|               4 |                         8 |
|               4 |                         9 |
|               4 |                        10 |
|               3 |                        11 |
|               3 |                        12 |
|               2 |                        13 |
+-----------------+---------------------------+

我想获取在 2010 年 DPC session 上至少发表过 2 次文章的所有作者的列表。我获得了发表过某些内容的作者列表以及每个作者的出版物数量，但我无法获得“至少 2”因子。

我的以下查询

SELECT author.name, COUNT(name) 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 AND publication.date=2010 AND publication.conference='DPC'GROUP BY author.name;

返回以下结果(很好)

+-------+-------------+
| name  | COUNT(name) |
+-------+-------------+
| Bob   |           2 |
| Bryan |           3 |
| John  |           1 |
+-------+-------------+

但是当我尝试仅选择 count(name)>=2 的那个时，出现错误。我尝试了这个查询:

SELECT author.name, COUNT(name) 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 AND publication.date=2010 AND publication.conference='DPC'GROUP BY author.name WHERE COUNT(name)>=2;

Answer 1

当您使用聚合函数时，您可以使用名为 HAVING 的适当运算符进行过滤

处理查询结果(然后 pn 聚合结果，如 count() )，而不是处理表行的原始值

SELECT author.name, COUNT(name) 
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 
AND publication.date=2010 AND publication.conference='DPC'
GROUP BY author.name 
HAVING COUNT(name)>=2;