MySQL 如何根据多行中的最大值选择单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8723404/
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
How to select single row based on the max value in multiple rows
提问by Brian
Possible Duplicate:
SQL: Find the max record per group
可能的重复：
SQL：查找每组的最大记录
I have a table with four columns as such:
我有一个四列的表格，如下所示：
name major minor revision
p1 0 4 3
p1 1 0 0
p1 1 1 4
p2 1 1 1
p2 2 5 0
p3 3 4 4
This is basically ca table containing records for each version of a program. I want to do a select to get all of the programs and their latest version so the results would look like this:
这基本上是包含每个程序版本记录的 ca 表。我想做一个选择以获取所有程序及其最新版本，因此结果如下所示：
name major minor revision
p1 1 1 4
p2 2 5 0
p3 3 4 4
I can't just group by the name and get the max of each column because then i would just end up with the highest number from each column, but not the specific row with the highest version. How can I set this up?
我不能只按名称分组并获得每列的最大值，因为这样我最终会得到每列中的最高数字，而不是具有最高版本的特定行。我该如何设置？
采纳答案by Jonathan Leffler
The way I try to solve SQL problems is to take things step by step.
我尝试解决 SQL 问题的方法是一步一步来。
- You want the maximum revision for the maximum minor version corresponding to the maximum major version for each product.
- 您需要与每个产品的最大主要版本相对应的最大次要版本的最大修订。
The maximum major number for each product is given by:
每个产品的最大主编号由下式给出：
SELECT Name, MAX(major) AS Major FROM CA GROUP BY Name;
The maximum minor number corresponding to the maximum major number for each product is therefore given by:
因此，对应于每个产品的最大主编号的最大次编号由下式给出：
SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
FROM CA
JOIN (SELECT Name, MAX(Major) AS Major
FROM CA
GROUP BY Name
) AS CB
ON CA.Name = CB.Name AND CA.Major = CB.Major
GROUP BY CA.Name, CA.Major;
And the maximum revision (for the maximum minor version number corresponding to the maximum major number for each product), therefore, is given by:
因此，最大修订版本（对应于每个产品的最大主版本号的最大次版本号）由下式给出：
SELECT CA.Name, CA.Major, CA.Minor, MAX(CA.Revision) AS Revision
FROM CA
JOIN (SELECT CA.Name, CA.Major, MAX(CA.Minor) AS Minor
FROM CA
JOIN (SELECT Name, MAX(Major) AS Major
FROM CA
GROUP BY Name
) AS CB
ON CA.Name = CB.Name AND CA.Major = CB.Major
GROUP BY CA.Name, CA.Major
) AS CC
ON CA.Name = CC.Name AND CA.Major = CC.Major AND CA.Minor = CC.Minor
GROUP BY CA.Name, CA.Major, CA.Minor;
Tested - it works and produces the same answer as Andomar's querydoes.
已测试 - 它可以工作并产生与Andomar的查询相同的答案。
Performance
表现
I created a bigger volume of data (11616 rows of data), and ran a benchmark timing of Andomar's query against mine - target DBMS was IBM Informix Dynamic Server (IDS) version 11.70.FC2 running on MacOS X 10.7.2. I used the first of Andomar's two queries since IDS does not support the comparison notation in the second one. I loaded the data, updated statistics, and ran the queries both with mine followed by Andomar's and with Andomar's followed by mine. I also recorded the basic costs reported by the IDS optimizer. The result data from both queries were the same (so the queries are both accurate - or equally inaccurate).
我创建了更大的数据量（11616 行数据），并运行了 Andomar 对我的查询的基准计时 - 目标 DBMS 是在 MacOS X 10.7.2 上运行的 IBM Informix Dynamic Server (IDS) 版本 11.70.FC2。我使用了 Andomar 的两个查询中的第一个，因为 IDS 不支持第二个查询中的比较符号。我加载了数据，更新了统计信息，并运行了我的查询，然后是 Andomar 的，以及 Andomar 的，然后是我的。我还记录了 IDS 优化器报告的基本成本。两个查询的结果数据相同（因此查询都是准确的 - 或同样不准确）。
Table unindexed:
表未索引：
Andomar's query Jonathan's query
Time: 22.074129 Time: 0.085803
Estimated Cost: 2468070 Estimated Cost: 22673
Estimated # of Rows Returned: 5808 Estimated # of Rows Returned: 132
Temporary Files Required For: Order By Temporary Files Required For: Group By
Table with unique index on (name, major, minor, revision):
具有唯一索引的表（名称、主要、次要、修订）：
Andomar's query Jonathan's query
Time: 0.768309 Time: 0.060380
Estimated Cost: 31754 Estimated Cost: 2329
Estimated # of Rows Returned: 5808 Estimated # of Rows Returned: 139
Temporary Files Required For: Group By
As you can seen, the index dramatically improves the performance of Andomar's query, but it still seems to be more expensive on this system than my query. The index gives a 25% time saving for my query. I'd be curious to see comparable figures for the two versions of Andomar's query on comparable volumes of data, with and without the index. (My test data can be supplied if you need it; there were 132 products - the 3 listed in the question and 129 new ones; each new product had (the same) 90 version entries.)
如您所见，索引显着提高了 Andomar 查询的性能，但在这个系统上它似乎仍然比我的查询更昂贵。该索引为我的查询节省了 25% 的时间。我很想看到 Andomar 对可比数据量的查询的两个版本的可比数据，有和没有索引。（如果需要，我的测试数据可以提供；有 132 个产品 - 问题中列出的 3 个和 129 个新产品；每个新产品都有（相同的）90 个版本条目。）
The reason for the discrepancy is that the sub-query in Andomar's query is a correlated sub-query, which is a relatively expensive process (dramatically so when the index is missing).
产生差异的原因是Andomar的查询中的子查询是相关的子查询，这是一个相对昂贵的过程（当索引丢失时会如此）。
回答by Andomar
You can use a not existssubquery to filter out older records:
您可以使用not exists子查询来过滤掉旧记录：
select *
from YourTable yt
where not exists
(
select *
from YourTable older
where yt.name = older.name and
(
yt.major < older.major or
yt.major = older.major and yt.minor < older.minor or
yt.major = older.major and yt.minor = older.minor and
yt.revision < older.revision
)
)
which can also be written in MySQL as:
也可以用 MySQL 写成：
select *
from YourTable yt
where not exists
(
select *
from YourTable older
where yt.name = older.name and
(yt.major, yt.minor, yt.revision)
< (older.major, older.major, older.revision)
)
回答by ypercube??
SELECT cam.*
FROM
( SELECT DISTINCT name
FROM ca
) AS cadistinct
JOIN
ca AS cam
ON ( cam.name, cam.major, cam.minor, cam.revision )
= ( SELECT name, major, minor, revision
FROM ca
WHERE name = cadistinct.name
ORDER BY major DESC
, minor DESC
, revision DESC
LIMIT 1
)
This will work in MySQL (current versions) but I woudn't recommend it:
这将适用于 MySQL（当前版本），但我不推荐它：
SELECT *
FROM
( SELECT name, major, minor, revision
FROM ca
ORDER BY name
, major DESC
, minor DESC
, revision DESC
) AS tmp
GROUP BY name
回答by Florin Ghita
Update3variable group_concat_max_len has a minvalue = 4 so we can't use it. But you can:
Update3变量 group_concat_max_len 有一个 minvalue = 4，所以我们不能使用它。但是你可以：
select
name,
SUBSTRING_INDEX(group_concat(major order by major desc),',', 1) as major,
SUBSTRING_INDEX(group_concat(minor order by major desc, minor desc),',', 1)as minor,
SUBSTRING_INDEX(group_concat(revision order by major desc, minor desc, revision desc),',', 1) as revision
from your_table
group by name;
this was tested hereand no, the previous version does not provide wrong results, it had only the problem with number of concatenated values.
这是在这里测试的，没有，以前的版本没有提供错误的结果，它只有连接值的数量问题。
回答by SWeko
If there are numbers in those columns, you could come up with some kind of a formula that will be unique and well ordered for the major, minor, revision values. E.g. if the numbers are less than 10, you could just append them as strings, and compare them, like:
如果这些列中有数字，您可以想出某种公式，该公式对于主要、次要和修订值是唯一且有序的。例如，如果数字小于 10，您可以将它们附加为字符串，然后比较它们，例如：
select name, major, minor, revision,
concat(major, minor, revision) as version
from versions
If they are numbers that will not be larger than 100, you could do something like:
如果它们是不大于 100 的数字，您可以执行以下操作：
select name, major, minor, revision,
(major * 10000 + minor * 100 + revision) as version
from versions
You could than just get the maxof versiongrouped by name, like this:
你可以比刚刚拿到max的version名字分组，就像这样：
select name, major, minor, revision
from (
select name, major, minor, revision,
(major * 10000 + minor * 100 + revision) as version
from versions) v1
where version = (select max (major * 10000 + minor * 100 + revision)
from versions v2
where v1.name = v2.name)
回答by aF.
Am I the only one thinking that the greatest version is the one with the highest revision?
只有我一个人认为最好的版本是最高版本的吗？
So,
所以，
select a.name, a.major, a.minor, a.revision
from table a
where a.revision = (select max(b.revision) from table b where b.name = a.name)
回答by Micha? Powaga
It allows max three digits per part of version number. If you want to use more digits then add two zeros to major multiplication an one zero to minor multiplication for each digit (I hope it's clear).
它允许每个版本号的每个部分最多三位数。如果你想使用更多的数字，那么在主乘法上加两个零，每个数字加一个零到次要乘法（我希望很清楚）。
select t.*
from yourTable t
join (
select name, max(major * 1000000 + minor * 1000 + revision) as ver
from yourTable
group by name
) t1 on t1.ver = (t.major * 1000000 + t.minor * 1000 + t.revision)
Result:
结果：
name major minor revision
p1 1 1 4
p2 2 5 0
p3 3 4 4
