如何选择MySQL中每个组的第一行? [英] How to select the first row for each group in MySQL?
本文介绍了如何选择MySQL中每个组的第一行?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
table
.GroupBy(row => row.SomeColumn )
.Select(group => group
.OrderBy(row => row.AnotherColumn)
.First()
)
$ c $ Linq-To-Sql将其转换为以下T-SQL代码:
<$ 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屋!
查看全文