MySQL：使用COUNT（）从子查询中选择MAX（） [英] MySQL: Select MAX() from sub-query with COUNT()

问题描述

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

Before you mark this as duplicate please take a look at this 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 |

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

Now, the tricky part here is the result of this query:

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 |

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

What do you think is wrong in the query?

推荐答案

您可以使用 LIMIT 代替 MAX（）对于相同的。也可以使用 JOIN 代替。

Instead of MAX() you can simply use LIMIT for the same. Also use JOIN instead.

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

输出：

| BOOK_COUNT | AUTHOR_ID |    FN |     LN |
-------------------------------------------
|          2 |        12 | name1 | lname1 |

请参见此SQLFiddle

---

编辑：

如果要使用 MAX（），则必须使用如下子查询：

If you want to use MAX() for that, you have to use sub-query like this:

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
WHERE book_count = 
        (SELECT MAX(book_count)
        FROM
        (
           SELECT c.author_id,COUNT(*) book_count FROM book c 
           GROUP BY c.author_id
        ) b )

请参见此SQLFiddle

---

Edit2：

除了在外部查询中使用 LIMIT 外，您还可以直接使用它内部查询也是如此：

Instead of using LIMIT in outer query you can simply use it in inner query too:

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
  ORDER BY COUNT(*) DESC LIMIT 1
) b 
ON a.author_id = b.author_id

这篇关于MySQL：使用COUNT（）从子查询中选择MAX（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！