当前位置： 文江博客话题详情

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

发布于 2024-08-30 14:41:29 字数 806 浏览 7 评论 0原文

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

原文

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（16）

甜嗑 2024-09-06 14:41:29

我的答案仅基于您帖子的标题，因为我不懂 C#，也不理解给定的查询。但在 MySQL 中我建议你尝试子选择。首先获取一组感兴趣的列的主键，然后从这些行中选择数据：

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

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:

SELECT somecolumn, anothercolumn 
  FROM sometable 
 WHERE id IN (
               SELECT min(id) 
                 FROM sometable 
                GROUP BY somecolumn
             );
回复 原文
汐鸠 2024-09-06 14:41:29

您可以尝试另一种方法，该方法不需要 ID 字段。

select some_column, min(another_column)
  from i_have_a_table
 group by some_column

我仍然同意lfagundes的观点，即您应该添加一些主键..

还要注意，通过这样做，您无法（轻松）获得其他值与生成的some_colum，another_column对在同一行！你需要 lfagundes apprach 和 PK 才能做到这一点！

Here's another way you could try, that doesn't need that ID field.

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 ..

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!

回复 原文
半边脸i 2024-09-06 14:41:29

当我写的时候

SELECT AnotherColumn
FROM Table
GROUP BY SomeColumn
;

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

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

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

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
;
回复 原文
相思故 2024-09-06 14:41:29

最佳性能且易于使用：

SELECT id, code,
SUBSTRING_INDEX(
     GROUP_CONCAT(price ORDER BY id DESC SEPARATOR '|'), '|', 1
) first_found_price
FROM stocks
GROUP BY code
ORDER BY id DESC

Best performance and easy to use:

SELECT id, code,
SUBSTRING_INDEX(
     GROUP_CONCAT(price ORDER BY id DESC SEPARATOR '|'), '|', 1
) first_found_price
FROM stocks
GROUP BY code
ORDER BY id DESC
回复 原文
沧笙踏歌 2024-09-06 14:41:29
SELECT
    t1.*

FROM
    table_name AS t1

    LEFT JOIN table_name AS t2 ON (
        t2.group_by_column = t1.group_by_column
        -- group_by_column is the column you would use in the GROUP BY statement
        AND
        t2.order_by_column < t1.order_by_column
        -- order_by_column is column you would use in the ORDER BY statement
        -- usually is the autoincremented key column
    )

WHERE
    t2.group_by_column IS NULL;

使用 MySQL v8+，您可以使用窗口函数

SELECT
    t1.*

FROM
    table_name AS t1

    LEFT JOIN table_name AS t2 ON (
        t2.group_by_column = t1.group_by_column
        -- group_by_column is the column you would use in the GROUP BY statement
        AND
        t2.order_by_column < t1.order_by_column
        -- order_by_column is column you would use in the ORDER BY statement
        -- usually is the autoincremented key column
    )

WHERE
    t2.group_by_column IS NULL;

With MySQL v8+ you could use window functions

回复 原文
琉璃繁缕 2024-09-06 14:41:29

我建议使用MySql官方的这种方式：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

通过这种方式，我们可以获得每篇文章的最高价格

I suggest to use this official way from MySql:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article
              GROUP BY s2.article)
ORDER BY article;

With this way, we can get the highest price on each article

回复 原文
聽兲甴掵 2024-09-06 14:41:29

您应该使用一些聚合函数来获取所需的 AnotherColumn 的值。也就是说，如果您想要 SomeColumn 的每个值（按数字或按字典顺序）的 AnotherColumn 的最低值，您可以使用：

SELECT SomeColumn, MIN(AnotherColumn)
FROM YourTable
GROUP BY SomeColumn

一些希望有用的链接：

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

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:

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://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

回复 原文
公布 2024-09-06 14:41:29

来自 MySQL 5.7 文档

MySQL 5.7.5及以上版本实现了函数依赖检测。如果启用 ONLY_FULL_GROUP_BY SQL 模式（默认情况下），MySQL 将拒绝选择列表、HAVING 条件或 ORDER BY 列表引用既未在 GROUP BY 子句中命名也不在功能上依赖于它们的非聚合列的查询.

这意味着 @Jader Dias 的解决方案并不适用于所有地方。

以下是启用 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;

From MySQL 5.7 documentation

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.

This means that @Jader Dias's solution wouldn't work everywhere.

Here is a solution that would work when ONLY_FULL_GROUP_BY is enabled:

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;
回复 原文
苦行僧 2024-09-06 14:41:29

rtribaldos 提到，在较新的数据库版本中，可以使用窗口函数。
这是一个对我有用的代码，并且与 Martin Zwarík 的 substring_index 解决方案（在 Mariadb 10.5.16 中）一样快：

SELECT group_col, order_col FROM (
  SELECT group_col, order_col
  , ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY order_col) rnr 
  FROM some_table
  WHERE <some_condition>
) i
WHERE rnr=1;

rtribaldos mentioned that in younger database versions, window-functions could be used.
Here is a code which worked for me and was as fast as Martin Zwarík's substring_index-solution (in Mariadb 10.5.16):

SELECT group_col, order_col FROM (
  SELECT group_col, order_col
  , ROW_NUMBER() OVER(PARTITION BY group_col ORDER BY order_col) rnr 
  FROM some_table
  WHERE <some_condition>
) i
WHERE rnr=1;
回复 原文
岁月如刀 2024-09-06 14:41:29

我在答案中没有看到以下解决方案，所以我想我应该把它放在那里。

问题是在按 SomeColumn 分组的所有组中选择按 AnotherColumn 排序时的第一行。

以下解决方案将在 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)

有一个针对 FIRST() 的功能请求，并且MySQL bug 跟踪器中的 LAST() ，但它在很多年前就被关闭了。

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.

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).

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.

回复 原文
夏日落 2024-09-06 14:41:29

这个怎么样：

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

How about this:

SELECT SUBSTRING_INDEX(
      MIN(CONCAT(OrderColumn, '|', IFNULL(TargetColumn, ''))
    ), '|', -1) as TargetColumn
FROM table
GROUP BY GroupColumn
回复 原文
半窗疏影 2024-09-06 14:41:29

另一种方法是

从在视图中工作的组中选择 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"

Yet another way to do it

Select max from group that works in views

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"
回复 原文
小糖芽 2024-09-06 14:41:29

另一种方法（没有主键）是使用 JSON 函数：

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
  from sometable group by somecolumn

或 pre 5.7.22

select somecolumn, 
  json_unquote( 
    json_extract( 
      concat('["', group_concat(othercolumn separator '","') ,'"]') 
    ,"$[0]" ) 
  ) 
  from sometable group by somecolumn

排序（或过滤）可以在分组之前完成：

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) 
  from (select * from sometable order by othercolumn) as t group by somecolumn

...或在分组之后（当然）：

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) as other 
  from sometable group by somecolumn order by other

不可否认，这相当令人费解，性能可能不是很好（没有在大数据上测试它，在我有限的数据集上运行良好）。

Yet another way to do it (without the primary key) would be using the JSON functions:

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") )
  from sometable group by somecolumn

or pre 5.7.22

select somecolumn, 
  json_unquote( 
    json_extract( 
      concat('["', group_concat(othercolumn separator '","') ,'"]') 
    ,"$[0]" ) 
  ) 
  from sometable group by somecolumn

Ordering (or filtering) can be done before grouping:

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) 
  from (select * from sometable order by othercolumn) as t group by somecolumn

... or after grouping (of course):

select somecolumn, json_unquote( json_extract(json_arrayagg(othercolumn), "$[0]") ) as other 
  from sometable group by somecolumn order by other

Admittedly, it's rather convoluted and performance is probably not great (didn't test it on large data, works well on my limited data sets).

回复 原文
看春风乍起 2024-09-06 14:41:29

我最近发现了一个很酷的技巧来实现这一点。基本上只是从表中创建两个不同的子查询并将它们连接在一起。其中一个子查询基于分组进行聚合，另一个子查询仅获取每个分组项的第一个 DISTINCT 行。

当您将这些子查询连接在一起时，您将获得每个组中的第一个不同的项目，但还将获得整个组中每个项目的聚合列。这与关闭 ONLY_FULL_GROUP_BY 的结果本质上相同。

SELECT non_aggregated_data.foo_column AS foo_column,
       non_aggregated_data.bar_column AS bar_column,
       aggregated_data.value_1_sum    AS value_1_sum,
       aggregated_data.value_2_sum    AS value_2_sum
FROM (SELECT column_to_self_join_on,
             sum(value_1) AS value_1_sum,
             sum(value_2) AS value_2_sum
      FROM example_table
      GROUP BY column_to_self_join_on) AS aggregated_data
         LEFT JOIN (SELECT DISTINCT(column_to_self_join_on),
                                   foo_column,
                                   bar_column
                    FROM example_table) AS non_aggregated_data
                   ON non_aggregated_data.column_to_self_join_on = aggregated_data.column_to_self_join_on

I recently discovered a cool trick to accomplish this. Basically just make two different subqueries from a table and join them together. One of the subqueries does the aggregation based on a grouping, and the other subquery just grabs the first DISTINCT row for each grouped item.

When you join these subqueries together, you will get the first distinct item from each group, but will also get the aggregated columns across the whole group for each item. This is essentially the same result as having ONLY_FULL_GROUP_BY turned off.

SELECT non_aggregated_data.foo_column AS foo_column,
       non_aggregated_data.bar_column AS bar_column,
       aggregated_data.value_1_sum    AS value_1_sum,
       aggregated_data.value_2_sum    AS value_2_sum
FROM (SELECT column_to_self_join_on,
             sum(value_1) AS value_1_sum,
             sum(value_2) AS value_2_sum
      FROM example_table
      GROUP BY column_to_self_join_on) AS aggregated_data
         LEFT JOIN (SELECT DISTINCT(column_to_self_join_on),
                                   foo_column,
                                   bar_column
                    FROM example_table) AS non_aggregated_data
                   ON non_aggregated_data.column_to_self_join_on = aggregated_data.column_to_self_join_on
回复 原文
锦爱 2024-09-06 14:41:29

在 Mysql 中选择每个组的第一行（按列排序）。

我们有：

一个表：mytable
我们排序的列：the_column_to_order_by
我们希望分组的列：the_group_by_column

这是我的解决方案。
内部查询为您提供一组唯一的行，选择作为双键。
外部查询通过连接这两个键（使用 AND）来连接同一个表。

SELECT * FROM 
    ( 
        SELECT the_group_by_column, MAX(the_column_to_order_by) the_column_to_order_by 
        FROM mytable 
        GROUP BY the_group_by_column 
        ORDER BY MAX(the_column_to_order_by) DESC 
    ) as mytable1 
JOIN mytable mytable2 ON mytable2.the_group_by_column = 
mytablealiamytable2.the_group_by_column 
  AND mytable2.the_column_to_order_by = mytable1.the_column_to_order_by;

仅供参考：我根本没有考虑过这方面的效率，也无法以某种方式谈论这一点。

Select the first row for each group (as ordered by a column) in Mysql .

We have:

a table: mytable
a column we are ordering by: the_column_to_order_by
a column that we wish to group by: the_group_by_column

Here's my solution.
The inner query gets you a unique set of rows, selected as a dual key.
The outer query joins the same table by joining on both of those keys (with AND).

SELECT * FROM 
    ( 
        SELECT the_group_by_column, MAX(the_column_to_order_by) the_column_to_order_by 
        FROM mytable 
        GROUP BY the_group_by_column 
        ORDER BY MAX(the_column_to_order_by) DESC 
    ) as mytable1 
JOIN mytable mytable2 ON mytable2.the_group_by_column = 
mytablealiamytable2.the_group_by_column 
  AND mytable2.the_column_to_order_by = mytable1.the_column_to_order_by;

FYI: I haven't thought about efficiency at all for this and can't speak to that one way or the other.

回复 原文
﹎☆浅夏丿初晴 2024-09-06 14:41:29

为什么不使用 MySQL LIMIT 关键字？

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

Why not use MySQL LIMIT keyword?

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
回复 原文
~没有更多了~

关于作者

闻呓

暂无简介

0 文章
0 评论
24 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文