MySQL SQL查询以获取与另一列的MAX值对应的列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6807854/
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
SQL Query to get column values that correspond with MAX value of another column?
提问by Devin
Ok, this is my query:
好的，这是我的查询：
SELECT
video_category,
video_url,
video_date,
video_title,
short_description,
MAX(video_id)
FROM
videos
GROUP BY
video_category
When it pulls the data, I get the correct row for the video_id, but it pulls the first row for each category for the others. So when I get the max result for the video_id of category 1, I get the max ID, but the first row in the table for the url, date, title, and description.
当它提取数据时，我得到了 video_id 的正确行，但它为其他类别提取了每个类别的第一行。因此，当我获得类别 1 的 video_id 的最大结果时，我获得了最大 ID，但获得了表中 url、日期、标题和描述的第一行。
How can I have it pull the other columns that correspond with the max ID result?
我怎样才能让它拉出与最大 ID 结果相对应的其他列？
Edit: Fixed.
编辑：固定。
SELECT
*
FROM
videos
WHERE
video_id IN
(
SELECT
DISTINCT
MAX(video_id)
FROM
videos
GROUP BY
video_category
)
ORDER BY
video_category ASC
采纳答案by Dalen
I would try something like this:
我会尝试这样的事情：
SELECT
s.video_id
,s.video_category
,s.video_url
,s.video_date
,s.video_title
,short_description
FROM videos s
JOIN (SELECT MAX(video_id) AS id FROM videos GROUP BY video_category) max
ON s.video_id = max.id
which is quite faster that your own solution
这比您自己的解决方案要快得多
回答by Steven Moseley
I'm late to the party here, but I just published a blog post on a method I've used for this for a couple years, and I thought I'd share this with the world.
我参加聚会迟到了，但我刚刚发表了一篇关于我已经使用了几年的方法的博客文章，我想我会与全世界分享这个方法。
I call the method Scalar Aggregate Comparison, and it's by far the highest-performance approach and simplest method (in DB engine terms) for accomplishing this, because it requires no joins, no subqueries, and no CTE.
我将方法称为Scalar Aggregate Comparison，它是迄今为止实现此目的的最高性能方法和最简单的方法（用数据库引擎术语），因为它不需要连接、子查询和 CTE。
For your query, it would look something like this:
对于您的查询，它看起来像这样：
SELECT
video_category,
MAX(video_id) AS video_id,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS video_url,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS video_date,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_title)), 12) AS video_title,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), short_description)), 12) AS short_description
FROM
videos
GROUP BY
video_category
The combination of scalar and aggregate functions does the following:
标量函数和聚合函数的组合执行以下操作：
- LPADs the comparison identifier to allow proper string comparison (e.g. "0009" and "0025" will be properly ranked). I'm LPADDING to 11 characters here assuming an INT primary key. If you use a BIGINT, you will want to increase this to support your table's ordinality. If you're comparing on a DATETIME field (fixed length), no padding is necessary.
- CONCATs the padded identifier with the output column (so you get "0009myvalue" vs "0025othervalue")
- MAX the aggregate set, which will yield "0025othervalue" as the winner.
- SUBSTRING the result, which will truncate the compared identifier portion, leaving only the value.
- LPADs 比较标识符以允许正确的字符串比较（例如“0009”和“0025”将被正确排序）。假设 INT 主键，我在这里 LPADDING 到 11 个字符。如果您使用 BIGINT，您将需要增加它以支持您的表的序数。如果您在 DATETIME 字段（固定长度）上进行比较，则不需要填充。
- 使用输出列连接填充标识符（因此您会得到“0009myvalue”与“0025othervalue”）
- MAX 聚合集，这将产生“0025othervalue”作为获胜者。
- SUBSTRING 结果，这将截断比较的标识符部分，只留下值。
If you want to retrieve values in types other than CHAR, you may need to performa an additional CAST on the output, e.g. if you want video_dateto be a DATETIME:
如果您想检索 CHAR 以外类型的值，您可能需要对输出执行额外的 CAST，例如，如果您想video_date成为 DATETIME：
CAST(SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS DATETIME)
CAST(SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_date)), 12) AS DATETIME)
Another benefit of this method over the self-joining method is that you can combine other aggregate data (not just latest values), or even combine first AND last item in the same query, e.g.
与自联接方法相比，此方法的另一个好处是您可以组合其他聚合数据（不仅仅是最新值），甚至可以组合同一查询中的第一个和最后一个项目，例如
SELECT
-- Overall totals
video_category,
COUNT(1) AS videos_in_category,
DATEDIFF(MAX(video_date), MIN(video_date)) AS timespan,
-- Last video details
MAX(video_id) AS last_video_id,
SUBSTRING(MAX(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS last_video_url,
...
-- First video details
MIN(video_id) AS first_video_id,
SUBSTRING(MIN(CONCAT(LPAD(video_id, 11, '0'), video_url)), 12) AS first_video_url,
...
-- And so on
For further details explaining the benefits of this method vs other older methods, my full blog post is here: https://www.stevenmoseley.com/high-performance-correlated-aggregate-sql-queries-without-ctes
有关解释此方法与其他旧方法的优点的更多详细信息，我的完整博客文章在这里：https: //www.stevenmoseley.com/high-performance-correlated-aggregate-sql-queries-without-ctes
回答by Guillaume Massé
Here is a more general solution (handles duplicates)
这是一个更通用的解决方案（处理重复项）
CREATE TABLE test(
i INTEGER,
c INTEGER,
v INTEGER
);
insert into test(i, c, v)
values
(3, 1, 1),
(3, 2, 2),
(3, 3, 3),
(4, 2, 4),
(4, 3, 5),
(4, 4, 6),
(5, 3, 7),
(5, 4, 8),
(5, 5, 9),
(6, 4, 10),
(6, 5, 11),
(6, 6, 12);
SELECT t.c, t.v
FROM test t
JOIN (SELECT test.c, max(i) as mi FROM test GROUP BY c) j ON
t.i = j.mi AND
t.c = j.c
ORDER BY c;
