MySQL 如何在MySQL中为每个组选择第一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2739474/
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 the first row for each group in MySQL?
提问by Jader Dias
In C# it would be like this:
在 C# 中，它会是这样的：
table
.GroupBy(row => row.SomeColumn)
.Select(group => group
.OrderBy(row => row.AnotherColumn)
.First()
)
Linq-To-Sql translates it to the following T-SQL code:
Linq-To-Sql 将其转换为以下 T-SQL 代码：
SELECT [t3].[AnotherColumn], [t3].[SomeColumn]
FROM (
SELECT [t0].[SomeColumn]
FROM [Table] AS [t0]
GROUP BY [t0].[SomeColumn]
) AS [t1]
OUTER APPLY (
SELECT TOP (1) [t2].[AnotherColumn], [t2].[SomeColumn]
FROM [Table] AS [t2]
WHERE (([t1].[SomeColumn] IS NULL) AND ([t2].[SomeColumn] IS NULL))
OR (([t1].[SomeColumn] IS NOT NULL) AND ([t2].[SomeColumn] IS NOT NULL)
AND ([t1].[SomeColumn] = [t2].[SomeColumn]))
ORDER BY [t2].[AnotherColumn]
) AS [t3]
ORDER BY [t3].[AnotherColumn]
But it is incompatible with MySQL.
但它与 MySQL 不兼容。
采纳答案by Jader Dias
When I write
当我写
SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;
It works. IIRC in other RDBMS such statement is impossible, because a column that doesn't belongs to the grouping key is being referenced without any sort of aggregation.
有用。IIRC 在其他 RDBMS 中这样的语句是不可能的，因为不属于分组键的列在没有任何类型的聚合的情况下被引用。
This "quirk" behaves very closely to what I want. So I used it to get the result I wanted:
这种“怪癖”与我想要的非常接近。所以我用它来得到我想要的结果：
SELECT * FROM
(
SELECT * FROM `table`
ORDER BY AnotherColumn
) t1
GROUP BY SomeColumn
;
回答by lfagundes
I based my answer on the title of your post only, as I don't know C# and didn't understand the given query. But in MySQL I suggest you try subselects. First get a set of primary keys of interesting columns then select data from those rows:
我的回答仅基于您帖子的标题，因为我不懂 C# 并且不理解给定的查询。但是在 MySQL 中，我建议您尝试子选择。首先获取一组有趣列的主键，然后从这些行中选择数据：
SELECT somecolumn, anothercolumn
FROM sometable
WHERE id IN (
SELECT min(id)
FROM sometable
GROUP BY somecolumn
);
回答by lexu
Here's another way you could try, that doesn't need that ID field.
这是您可以尝试的另一种方法，它不需要该 ID 字段。
select some_column, min(another_column)
from i_have_a_table
group by some_column
Still I agree with lfagundes that you should add some primary key ..
我仍然同意 lfagundes 您应该添加一些主键..
Also beware that by doing this, you cannot (easily) get at the other values is the same row as the resulting some_colum, another_column pair! You'd need lfagundes apprach and a PK to do that!
还要注意，通过这样做，您不能（轻松）获得其他值与结果 some_colum, another_column 对相同的行！你需要 lfagundes apprach 和 PK 来做到这一点！
回答by David M
You should use some aggregate function to get the value of AnotherColumn that you want. That is, if you want the lowest value of AnotherColumn for each value of SomeColumn (either numerically or lexicographically), you can use:
您应该使用一些聚合函数来获取您想要的 AnotherColumn 的值。也就是说，如果您想要 SomeColumn 的每个值（数字或字典顺序）的 AnotherColumn 的最低值，您可以使用：
SELECT SomeColumn, MIN(AnotherColumn)
FROM YourTable
GROUP BY SomeColumn
Some hopefully helpful links:
一些希望有用的链接：
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html
http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html
http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html
回答by Nicolai
MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.
MySQL 5.7.5 及更高版本实现了功能依赖检测。如果 ONLY_FULL_GROUP_BY SQL 模式被启用（默认情况下），MySQL 拒绝查询，其中选择列表、HAVING 条件或 ORDER BY 列表引用非聚合列，这些列既不在 GROUP BY 子句中命名，也不在功能上依赖它们.
This means that @Jader Dias's solution wouldn't work everywhere.
这意味着@Jader Dias 的解决方案不会在任何地方都有效。
Here is a solution that would work when ONLY_FULL_GROUP_BYis enabled:
这是一个在ONLY_FULL_GROUP_BY启用时有效的解决方案：
SET @row := NULL;
SELECT
SomeColumn,
AnotherColumn
FROM (
SELECT
CASE @id <=> SomeColumn AND @row IS NOT NULL
WHEN TRUE THEN @row := @row+1
ELSE @row := 0
END AS rownum,
@id := SomeColumn AS SomeColumn,
AnotherColumn
FROM
SomeTable
ORDER BY
SomeColumn, -AnotherColumn DESC
) _values
WHERE rownum = 0
ORDER BY SomeColumn;
回答by Ricardo Tribaldos
SELECT
t1.*
FROM
table_name AS t1
LEFT JOIN table_name AS t2 ON (
t2.group_by_column = t1.group_by_column
-- group_by_column is the column you would use in the GROUP BY statement
AND
t2.order_by_column < t1.order_by_column
-- order_by_column is column you would use in the ORDER BY statement
-- usually is the autoincremented key column
)
WHERE
t2.group_by_column IS NULL;
With MySQL v8+ you could use window functions
使用 MySQL v8+，您可以使用窗口函数
回答by Lars Nystr?m
I have not seen the following solution among the answers, so I thought I'd put it out there.
我在答案中没有看到以下解决方案，所以我想我会把它放在那里。
The problem is to select rows which are the first rows when ordered by AnotherColumnin all groups grouped by SomeColumn.
问题是AnotherColumn在按 分组的所有组中按顺序选择第一行SomeColumn。
The following solution will do this in MySQL. idhas to be a unique column which must not hold values containing -(which I use as a separator).
以下解决方案将在 MySQL 中执行此操作。id必须是一个唯一的列，它不能包含包含-（我用作分隔符）的值。
select t1.*
from mytable t1
inner join (
select SUBSTRING_INDEX(
GROUP_CONCAT(t3.id ORDER BY t3.AnotherColumn DESC SEPARATOR '-'),
'-',
1
) as id
from mytable t3
group by t3.SomeColumn
) t2 on t2.id = t1.id
-- Where
SUBSTRING_INDEX(GROUP_CONCAT(id order by AnotherColumn desc separator '-'), '-', 1)
-- can be seen as:
FIRST(id order by AnotherColumn desc)
-- For completeness sake:
SUBSTRING_INDEX(GROUP_CONCAT(id order by AnotherColumn desc separator '-'), '-', -1)
-- would then be seen as:
LAST(id order by AnotherColumn desc)
There is a feature requestfor FIRST()and LAST()in the MySQL bug tracker, but it was closed many years back.
有一个功能要求的FIRST()，并LAST()在MySQL的bug跟踪系统，但它被关闭多年了。
回答by Iikka
Yet another way to do it (without the primary key) would be using the JSON functions:
另一种方法（没有主键）是使用 JSON 函数：
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
from sometable group by somecolumn
or pre 5.7.22
或 5.7.22 之前的版本
select somecolumn,
json_unquote(
json_extract(
concat('["', group_concat(othercolumn separator '","') ,'"]')
,"$[0]" )
)
from sometable group by somecolumn
Ordering (or filtering) can be done before grouping:
排序（或过滤）可以在分组之前完成：
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
from (select * from sometable order by othercolumn) as t group by somecolumn
... or after grouping (of course):
...或分组后（当然）：
select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) as other
from sometable group by somecolumn order by other
Admittedly, it's rather convoluted and performance is probably not great (didn't test it on large data, works well on my limited data sets).
不可否认，它相当复杂，性能可能不是很好（没有在大数据上测试它，在我有限的数据集上运行良好）。
回答by Yura Fedoriv
How about this:
这个怎么样：
SELECT SUBSTRING_INDEX(
MIN(CONCAT(OrderColumn, '|', IFNULL(TargetColumn, ''))
), '|', -1) as TargetColumn
FROM table
GROUP BY GroupColumn
回答by Martin Zvarík
SELECT id, code,
SUBSTRING_INDEX( GROUP_CONCAT(price ORDER BY id DESC), ',', 1) first_found_price
FROM stocks
GROUP BY code
ORDER BY id DESC
