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

  • Z时代
  • 2024-01-10
  • 分类：问答

在C＃中将是这样的：

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

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]

但是它与MySQL不兼容。

回答：

当我写

SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;

有用。在其他RDBMS中，IIRC这样的语句是不可能的，因为不属于任何组合键的不属于分组键的列就被引用了。

这种“怪癖”的行为与我想要的行为非常接近。所以我用它来获得想要的结果：

SELECT * FROM 
(
 SELECT * FROM `table`
 ORDER BY AnotherColumn
) t1
GROUP BY SomeColumn
;

以上是 如何在MySQL中为每个组选择第一行？ 的全部内容， 来源链接： utcz.com/qa/421669.html

回到顶部