使用 HAVING 子句过滤 GROUP BY 结果

HAVING 子句过滤 GROUP BY 表达式的结果。注意：以下示例使用 Library 示例数据库。

例子：

返回所有撰写多本书的作者（ 实例 ）。

SELECT
  a.Id,
  a.Name,
  COUNT(*) BooksWritten
FROM BooksAuthors ba
  INNER JOIN Authors a ON a.id = ba.authorid
GROUP BY
  a.Id,
  a.Name
HAVING COUNT(*) > 1    -- equals to HAVING BooksWritten > 1
;

归还所有拥有三位以上作者的书籍（ 实例 ）。

SELECT
  b.Id,
  b.Title,
  COUNT(*) NumberOfAuthors
FROM BooksAuthors ba
  INNER JOIN Books b ON b.id = ba.bookid
GROUP BY
  b.Id,
  b.Title
HAVING COUNT(*) > 3    -- equals to HAVING NumberOfAuthors > 3
;