这个MySQL quearly会选择所有写过书的作者吗？

Question

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;

这不是正确的我知道，但我不能弄明白？？

Answer 1

假设需求是统计至少有一本书的作者，满足这一要求的最简单的查询是：

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表中有authorid的值，而这些值没有出现在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

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