返回SQL中带有“MAX”值的所有行?

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中)，但它只返回一行。

4 个解决方案

#1

SELECT Id, value
    FROM Scores
    WHERE value = (SELECT MAX(value) FROM Scores);

#2

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 =(子查询)相比，这两种方法都更可取，因为对于每一种方法，查找最大值的查询都只执行一次(这是绝对保证，所以我更喜欢基于变量的解决方案)。对于子查询版本(在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做了一些查询分析，内部连接方法可能是所有建议中最简洁、最优的(假设您所处的环境使用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)
• @ctrahey(两者都有):5(扫描一次找到MAX)
• @Joe Stefanelli: 25 (scans once per row (5*5))
• @Joe Stefanelli: 25(每行扫描一次(5*5))
• @Jocelyn : 17 (I can't explain this one, but I would love to learn why :-)
• @Jocelyn: 17(我无法解释这个，但我很想知道为什么:-)

#3

In MySQL you need to do this with a join or subquery:

在MySQL中，需要使用连接或子查询:

select *
from t
where value = (select max(value) from t)

#4

Use this query:

使用这个查询:

SELECT id, value
FROM Scores
WHERE value>=ALL(SELECT value FROM Scores)

Documentation: Subqueries with ALL

与所有文档:子查询

Optimizing Subqueries:

优化子查询:

MySQL enhances expressions of the following form with an expression involving MIN() or MAX(), unless NULL values or empty sets are involved:

MySQL通过包含MIN()或MAX()的表达式来增强以下表单的表达式，除非涉及NULL值或空集:

value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

For example, this WHERE clause:

例如，这个WHERE子句:

WHERE 5 > ALL (SELECT x FROM t)

might be treated by the optimizer like this:

可能会被这样的优化器处理:

WHERE 5 > (SELECT MAX(x) FROM t)