MySQL 在 SQL 中返回具有“MAX”值的所有行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12167809/
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
Return all rows with the "MAX" value in SQL?
提问by RidingRails
I have the following table:
我有下表：
Table: Scores What I have:
表：分数 我所拥有的：
+----+-------+ | Id | value | +----+-------+ | 1 | 300 | | 2 | 300 | | 3 | 300 | | 4 | 100 | | 5 | 200 | +----+-------+
What I need:
我需要的：
+----+-------+ | Id | value | +----+-------+ | 1 | 300 | | 2 | 300 | | 3 | 300 | --------------
How would I grab "all" the top scores id 1, 2, 3 in SQL. I started off using MAX (in mysql) but that only returns one row.
我将如何在 SQL 中获取“所有”最高分 id 1、2、3。我开始使用 MAX（在 mysql 中），但它只返回一行。
回答by Joe Stefanelli
SELECT Id, value
FROM Scores
WHERE value = (SELECT MAX(value) FROM Scores);
回答by Chris Trahey
Use a quick variable:
使用快速变量：
SELECT @max := max(value) from scores;
SELECT id, value FROM scores WHERE value = @max;
or else: (and I am normally in staunch opposition to sub-queries, but this one's a no-brainer.
否则：（我通常坚决反对子查询，但这很简单。
SELECT id, value FROM
scores
INNER JOIN (Select max(value) as value from scores) as max USING(value)
Note that these are both preferable to the more basic `WHERE value = (subquery) because for each of them, the query to find the MAX value is executed exactly once (the absolute guarantee of this is why I prefer the variable-based solution). With the subquery version (in the WHERE, not the JOIN), that query is likely to be executed once per row.
请注意，这些都比更基本的 `WHERE value = ( subquery)更可取，因为对于它们中的每一个，查找 MAX 值的查询只执行一次（绝对保证这就是为什么我更喜欢基于变量的解决方案） . 使用子查询版本（在 WHERE 中，而不是 JOIN 中），该查询很可能每行执行一次。
I have done some query analyzing with EXPLAIN EXTENDED, and the INNER JOIN method is probably the most succinct and optimal of all suggestions (supposing that you are in an environment where using MySQL variables is too cumbersome; I still think it is the cleanest).
我用 做了一些查询分析EXPLAIN EXTENDED，INNER JOIN 方法可能是所有建议中最简洁和最优的（假设您处于使用 MySQL 变量太麻烦的环境中；我仍然认为它是最干净的）。
performance:
表现：
Since some interesting discussion took place, I decided to really dig in and evaluate these things (overkill, I know, but fun and useful knowledge on bigger issues). There is a bit of an analysis trick for detecting full table scans; adding WHERE (@foo := @foo + 1)to the subqueries in question, then setting @foo to 0, running the query, and seeing what @foo is. It's not the end-all be-all query-toll metric, but it can be quite informative about how often you are asking MySQL to evaluate each row. Here are the "scores" with your sample data (lower is better):
由于发生了一些有趣的讨论，我决定真正深入挖掘并评估这些事情（我知道矫枉过正，但在更大的问题上有趣且有用的知识）。有一些检测全表扫描的分析技巧；添加WHERE (@foo := @foo + 1)到有问题的子查询中，然后将 @foo 设置为 0，运行查询，然后查看 @foo 是什么。这不是最终的全部查询收费指标，但它可以提供有关您要求 MySQL 评估每一行的频率的信息。以下是您的样本数据的“分数”（越低越好）：
- @ctrahey (both): 5 (scans once to find MAX)
- @Joe Stefanelli: 25 (scans once per row(5*5))
- @Jocelyn : 17 (I can't explain this one, but I would love to learn why :-)
- @ctrahey（两者）：5（扫描一次以找到 MAX）
- @Joe Stefanelli：25（每行扫描一次(5*5)）
- @Jocelyn：17（我无法解释这个，但我很想知道为什么:-)
回答by Gordon Linoff
In MySQL you need to do this with a join or subquery:
在 MySQL 中，您需要使用连接或子查询来执行此操作：
select *
from t
where value = (select max(value) from t)
回答by Jocelyn
Use this query:
使用此查询：
SELECT id, value
FROM Scores
WHERE value>=ALL(SELECT value FROM Scores)
Documentation: Subqueries with ALL
文档：使用 ALL 的子查询
MySQL enhances expressions of the following form with an expression involving MIN() or MAX(), unless NULL values or empty sets are involved:
value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)For example, this WHERE clause:
WHERE 5 > ALL (SELECT x FROM t)might be treated by the optimizer like this:
WHERE 5 > (SELECT MAX(x) FROM t)
MySQL 使用涉及 MIN() 或 MAX() 的表达式增强以下形式的表达式，除非涉及 NULL 值或空集：
value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)例如，这个 WHERE 子句：
WHERE 5 > ALL (SELECT x FROM t)优化器可能会像这样处理：
WHERE 5 > (SELECT MAX(x) FROM t)
