如何选择MySQL中每个组的第一行？ [英] How to select the first row for each group in MySQL?

问题描述

在C＃中它会是这样的：

  table 
 .GroupBy（row => row.SomeColumn ）
 .Select（group => group 
 .OrderBy（row => row.AnotherColumn）
 .First（）
）
  
 
 <$ p 
 $ [$] $ [$] $ [$] $ [$] $ [$] $ [$] $ [$] ] 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]不是NULL）和（[t2]。[SomeColumn]不是NULL）
 AND（[t1]。[SomeColumn] = [t2]。[ SomeColumn]））
 ORDER BY [t2]。[AnotherColumn] 
）AS [t3] 
 ORDER BY [t3]。[AnotherColumn] 
  

但它与wi不兼容当我写入

 
 
 
 code> SELECT AnotherColumn 
 FROM Table 
 GROUP BY SomeColumn 
; 
  

有效。 IIRC在其他RDBMS中这样的声明是不可能的，因为不属于分组键的列被引用而没有任何聚合。

这个怪癖行为非常接近我想要的。所以我用它来得到我想要的结果：

$ p $ SELECT * FROM
（
SELECT * FROM `table`
ORDER BY AnotherColumn
）t1
GROUP BY SomeColumn
;


In C# it would be like this:

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

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]

But it is incompatible with MySQL.

解决方案

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.

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
;

这篇关于如何选择MySQL中每个组的第一行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！