我想解决这个问题有很多困难。
编写一个查询来显示写过一本书的作者的数量
作者(AuthorID,AuthorName,Address,TelephoneNo,PublisherCode)
书籍(BookID,Name,ReleaseDate,Price,AuthorID)
我有过
SELECT a.AuthorName, COUNT(b.*) AS ‘number of books written’
FROM Author a JOIN Book b ON a.AuthorID = b.BookID
GROUP BY a.AuthorName;计算每个作者写的书的数量。
这不是我所知道的正确的,但我想不出来??
发布于 2019-11-22 16:46:12
假设需求是统计至少有一本书的作者,那么最简单的查询就是:
SELECT COUNT(DISTINCT b.authorid)
FROM book b我们可能希望为返回的列分配别名(名称):
SELECT COUNT(DISTINCT b.authorid) AS `count_of_authors_who_have_at_least_one_book`
FROM book b我们也可以对author表进行连接,但这在这里没有必要,除非book表中没有显示在author表中的值(即没有外键约束,或者引用完整性没有强制执行)
要获得有两本或两本以上书籍的作者的查询可能要复杂一些:
SELECT COUNT(*)
FROM ( -- authors of two or more books
SELECT b.authorid
FROM book b
GROUP
BY b.authorid
HAVING COUNT(1) >= 2
) c如果我们希望作者拥有一本书(而不是两本或两本以上),我们可以调整HAVING子句中的条件:
SELECT COUNT(*) AS `count_authors_of_exactly_one_book`
FROM ( -- authors of exactly one book
SELECT b.authorid
FROM book b
GROUP
BY b.authorid
HAVING COUNT(1) = 1
) c发布于 2019-11-22 16:28:29
你离我很近。您需要加入作者ID。您目前正在混合作者ID和图书ID,这将不能正确匹配。
SELECT
a.AuthorName,
COUNT(b.*) AS ‘number of books written’
FROM Author a
JOIN Book b ON a.AuthorID = b.AuthorID
GROUP BY a.AuthorName;发布于 2019-11-22 16:30:13
如果您只想得到一个表示至少在书上所写的作者总数的数字,请使用下面的查询
select count(*) as author_count from Author where exists (select 1 from Book where Book.AuthorID = Author.AuthorID)https://stackoverflow.com/questions/58998164
复制相似问题