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

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:

linqto - 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不兼容。

9 个解决方案

#1

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中，我建议您尝试使用subselect。首先获取一组感兴趣的列的主键，然后从这些行中选择数据:

SELECT somecolumn, anothercolumn 
  FROM sometable 
 WHERE id IN (
               SELECT min(id) 
                 FROM sometable 
                GROUP BY somecolumn
             );

#2

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.

它的工作原理。在其他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
;

#3

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来完成!

#4

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:

您应该使用某个聚合函数来获取您想要的另一列的值。也就是说，如果您想要对SomeColumn的每个值(无论是数值上的还是字典上的)使用另一列的最小值，您可以使用:

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

#5

From MySQL 5.7 documentation

从MySQL 5.7文档

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和up实现了功能依赖性的检测。如果启用了ONLY_FULL_GROUP_BY SQL模式(默认情况下是这样的)，那么MySQL将拒绝select列表、具有条件或按列表顺序引用的查询，这些列既不在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_BY is 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;

#6

Yet another way to do it

还有另一种方法

Select max from group that works in views

从视图中工作的组中选择max。

SELECT * FROM action a 
WHERE NOT EXISTS (
   SELECT 1 FROM action a2 
   WHERE a2.user_id = a.user_id 
   AND a2.action_date > a.action_date 
   AND a2.action_type = a.action_type
)
AND a.action_type = "CF"

#7

How about this:

这个怎么样:

SELECT SUBSTRING_INDEX(
      MIN(CONCAT(OrderColumn, '|', IFNULL(TargetColumn, ''))
    ), '|', -1) as TargetColumn
FROM table
GROUP BY GroupColumn

#8

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 AnotherColumn in all groups grouped by SomeColumn.

问题是，在所有组中按SomeColumn分组的所有组中，按照另一列的顺序选择第一行。

The following solution will do this in MySQL. id has 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 request for FIRST() and LAST() in the MySQL bug tracker, but it was closed many years back.

在MySQL bug跟踪器中，有一个关于FIRST()和LAST()的特性请求，但是它在许多年前就被关闭了。

#9

Why not use MySQL LIMIT keyword?

为什么不使用MySQL限制关键字?

SELECT [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]
LIMIT 1