使用 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
;