mysql count的子查询_MySQL：使用COUNT()从子查询中选择MAX()

在将其标记为重复之前,请先查看此SQLFiddle.

我有这个架构：

CREATE TABLE book(book_id int,

book_name varchar(100),

author_id int,

editor_id varchar(100),

isbn varchar(100));

INSERT INTO book

VALUES

(1 , 'Book1 Title' , 12 , 'Editor1' , '8000-9000' ),

(2 , 'Book2 Title' , 98 , 'Editor1' , '8000-9001' ),

(1 , 'Book1 Title' , 12 , 'Editor1' , '8000-9002' ),

(3 , 'Book3 Title' , 3 , 'Editor1' , '8000-9003' );

CREATE TABLE author(author_id int,

fn varchar(100),

ln varchar(100));

INSERT INTO author

VALUES

(12, 'name1','lname1'),

(98,'name2','lname2'),

(3,'name3','lname3');

子查询：

SELECT c.author_id,COUNT(*) book_count FROM book c

GROUP BY c.author_id

结果：

| AUTHOR_ID | BOOK_COUNT |

--------------------------

| 3 | 1 |

| 12 | 2 |

| 98 | 1 |

现在,这里最棘手的部分是此查询的结果：

SELECT MAX(book_count),a.* FROM

author a,(

SELECT c.author_id,COUNT(*) book_count FROM book c

GROUP BY c.author_id

) b

where a.author_id = b.author_id

这是：

| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN |

------------------------------------------------

| 2 | 3 | name3 | lname3 |

应该是这样的：

| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN |

------------------------------------------------

| 2 | 12 | name1 | lname1 |

您认为查询中有什么问题？