mysql - 计算列中具有相同值的行数

Question

我有这两个实体及其属性:

book - book_id, book_name, author_id, editor_id, subject_id, isbn

author - author_id, fn, ln

我要查询行数最多的作者这是属性和实体:

| book_id | book_name   | author_id |  editor_id |  isbn     |--------------------------------------------------------------|       1 | Book1 Title |  Author1  |  Editor1   | 8000-9000 ||       2 | Book2 Title |  Author2  |  Editor1   | 8000-9001 ||       1 | Book1 Title |  Author1  |  Editor1   | 8000-9002 ||       3 | Book2 Title |  Author2  |  Editor1   | 8000-9003 |

| author_id |  fn    |    ln    |---------------------------------|       1   | name1  |  lname1  ||       2   | name2  |  lname2  ||       3   | name3  |  lname3  | 

and here is my code:

SELECT author.author_id, author.fn, author.ln, COUNT(DISTINCT book.editor_id) as num
FROM `editor`, `book`
GROUP BY `editor_id`
LIMIT 0,1

但偏离路线我没有得到我想要的。输出必须是:

| author_id |  fn    |    ln    |---------------------------------|       1   | name1  |  lname1  |

因为“author_id = 1”在书上有 2 个条目。

Answer 1

试试这个，但请注意，我将 author_id 从 varchar 转换为 int):

SELECT book_count,a.author_id,a.fn, a.ln 
FROM author a
JOIN
(
  SELECT c.author_id,COUNT(*) book_count FROM book c
  GROUP BY c.author_id
) b 
ON a.author_id = b.author_id
ORDER BY book_count DESC LIMIT 1

这是 SQLFiddle .