MySQL：使用 COUNT() 从子查询中选择 MAX()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18051729/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Select MAX() from sub-query with COUNT()
提问by Christian Mark
Before you mark this as duplicate please take a look at this SQLFiddle.
在将其标记为重复之前，请查看此SQLFiddle。
I have this schema:
我有这个架构：
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');
The sub-query:
子查询：
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id
has a result:
有一个结果：
| 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
is this:
这是：
| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN |
------------------------------------------------
| 2 | 3 | name3 | lname3 |
which should be like this:
应该是这样的：
| MAX(BOOK_COUNT) | AUTHOR_ID | FN | LN |
------------------------------------------------
| 2 | 12 | name1 | lname1 |
What do you think is wrong in the query?
您认为查询中有什么问题？
采纳答案by hims056
Instead of MAX()you can simply use LIMITfor the same. Also use JOINinstead.
而不是MAX()你可以简单地使用LIMIT相同的。也可以JOIN改用。
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
Output:
输出：
| BOOK_COUNT | AUTHOR_ID | FN | LN |
-------------------------------------------
| 2 | 12 | name1 | lname1 |
See this SQLFiddle
看到这个 SQLFiddle
Edit:
编辑：
If you want to use MAX()for that, you have to use sub-query like this:
如果你想使用MAX()它，你必须使用这样的子查询：
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 )
See this SQLFiddle
看到这个 SQLFiddle
Edit2:
编辑2：
Instead of using LIMITin outer query you can simply use it in inner query too:
除了LIMIT在外部查询中使用之外，您也可以简单地在内部查询中使用它：
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
See this SQLFiddle
看到这个 SQLFiddle
回答by Mikhail Aksenov
In fact, MySQL has a lack of support SQL's standard, because it allows use aggregate functions w/o GROUP BY clause and returns random data in result. You should avoid the usage of aggregates in that way.
实际上，MySQL 缺乏对 SQL 标准的支持，因为它允许使用不带 GROUP BY 子句的聚合函数并在结果中返回随机数据。您应该避免以这种方式使用聚合。
EDIT: I mean, for example in MySQL you can execute query like this:
编辑：我的意思是，例如在 MySQL 中，您可以执行这样的查询：
SELECT
MAX(a), b, c
FROM
table
GROUP BY
b;
Which returns random data in c column, and that's terribly wrong.
它在 c 列中返回随机数据，这是非常错误的。
回答by Ashutosh Arya
This am wondering this query is running, you used aggregate function with out using group by. When you need to identity the user which has maximum nook count Please try
我想知道这个查询正在运行，您使用了聚合函数而不使用 group by。当您需要识别具有最大角落计数的用户时，请尝试
SELECT (book_count),b.author_id FROM
author a
INNER JOIN
(
SELECT c.author_id,COUNT(*) book_count FROM book c
GROUP BY c.author_id) B
ON
a.author_id = b.author_id
having book_count=MAX(book_count)
Let me know if it works.
让我知道它是否有效。
