如何在MySQL中为每个组选择第一行?

2021-12-26 00:00:00 group-by grouping mysql

在 C# 中是这样的:

In C# it would be like this:

table
   .GroupBy(row => row.SomeColumn)
   .Select(group => group
       .OrderBy(row => row.AnotherColumn)
       .First()
   )

Linq-To-Sql 将其转换为以下 T-SQL 代码:

Linq-To-Sql translates it to the following T-SQL code:

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]

但它与 MySQL 不兼容.

But it is incompatible with MySQL.

推荐答案

当我写作时

SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;

它有效.IIRC 在其他 RDBMS 这样的语句中是不可能的，因为不属于分组键的列被引用而没有任何类型的聚合.

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.

这种怪癖"与我想要的非常接近.所以我用它来得到我想要的结果:

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
;

相关文章