而不是
MAX()您可以简单地使用
LIMIT相同。也可以
JOIN改用。
SELECt book_count,a.author_id,a.fn, a.ln FROM author aJOIN( SELECt c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id) b ON a.author_id = b.author_idORDER BY book_count DESC LIMIT 1
输出：
| BOOK_COUNT | AUTHOR_ID | FN | LN |-------------------------------------------| 2 | 12 | name1 | lname1 |
看到这个SQLFiddle
编辑：
如果要使用
MAX()它，则必须使用如下子查询：
SELECt book_count,a.author_id,a.fn, a.ln FROM author aJOIN( SELECt c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id) b ON a.author_id = b.author_idWHERe book_count = (SELECt MAX(book_count) FROM (SELECt c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id ) b )
看到这个SQLFiddle
编辑2：
除了
LIMIT在外部查询中使用之外，您也可以在内部查询中使用它：
SELECt book_count,a.author_id,a.fn, a.ln FROM author aJOIN( SELECt c.author_id,COUNT(*) book_count FROM book c GROUP BY c.author_id ORDER BY COUNT(*) DESC LIMIT 1) b ON a.author_id = b.author_id
