MySQL通过选择组中的最大记录 [英] MySQL selecting max record in group by

问题描述

我试图在一个包含约50万条记录和50或60列的表中创建一个查询。我需要的是将这些记录整理成组，并选择每个组中的最大记录。

为了简化问题，我有一个表格如下所示：

  + ---- + ------------- + ---------- +  - ------- + 
 | id | external_id | group_id | mypath | 
 + ---- + ------------- + ---------- + -------- + 
 | 1 | 1003 | 1 | a | 
 | 2 | 1004 | 2 | b | 
 | 3 | 1005 | 2 | c | 
 + ---- + ------------- + ---------- + -------- + 
  

简单的分组如下所示：

  select * from temp GROUP BY group_id 
  

返回

  + ---- + ------------- + ---------- + --- ----- + 
 | id | external_id | group_id | mypath | 
 + ---- + ------------- + ---------- + -------- + 
 | 1 | 1003 | 1 | a | 
 | 2 | 1004 | 2 | b | 
 + ---- + ------------- + ---------- + -------- + 
  

很好，但不是我想要的。我想要的是每个组中max enternal_id的整个记录​​。换句话说

  + ---- + ------------- + --- ------- + -------- + 
 | id | external_id | group_id | mypath | 
 + ---- + ------------- + ---------- + -------- + 
 | 1 | 1003 | 1 | a | 
 | 3 | 1005 | 2 | c | 
 + ---- + ------------- + ---------- + -------- + 
  

不知何故，我希望在此放置一个max（external_id）语句来过滤需要的内容，但迄今为止我的调查都失败了。一些指导将不胜感激。重要的是，当返回max（external_id）时，整个记录被选为路径列是不同的。

解决方案

更多信息at http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

这在MySQL中一直是一个烦人的问题。有一些方法可以解决这个问题，比如将几个字段连接在一起（以external_id开头），然后选择MAX（），然后将其分开。

我建议你使用派生表。第一个表（t1）是从一个简单的查询中派生出来的，在这个查询中标识了 MAX（external_id），然后加入其中以获取其余数据。

如果 external_id IS独特

  SELECT 
 t1.group_id，some_table.id，some_table.mypath 
 FROM 
（
 SELECT group_id，MAX（external_id）AS external_id 
 FROM some_table 
 GROUP BY group_id 
）as t1 
 INNER JOIN 
 sometable ON t1.external_id = sometable.external_id 
 WHERE ... 
  

I am trying to create a query in a table that has some 500,000 records and some 50 or 60 columns. What I need is to collate these records into groups and select the max record in each group.

To simplify the problem I have a table as follows

+----+-------------+----------+--------+
| id | external_id | group_id | mypath |
+----+-------------+----------+--------+
|  1 |        1003 |        1 | a      | 
|  2 |        1004 |        2 | b      | 
|  3 |        1005 |        2 | c      | 
+----+-------------+----------+--------+

The simple group by is as follows

select * from temp GROUP BY group_id

which returns

+----+-------------+----------+--------+
| id | external_id | group_id | mypath |
+----+-------------+----------+--------+
|  1 |        1003 |        1 | a      | 
|  2 |        1004 |        2 | b      | 
+----+-------------+----------+--------+

Nice but not what I want. What I want is the entire record for max enternal_id in each group. In other words

+----+-------------+----------+--------+
| id | external_id | group_id | mypath |
+----+-------------+----------+--------+
|  1 |        1003 |        1 | a      | 
|  3 |        1005 |        2 | c      | 
+----+-------------+----------+--------+

Somehow I am looking to put a max(external_id) statement in here to filter what is needed but so far all my investigation has failed. Some guidance would be appreciated. It is important that when returning the max(external_id) that the entire record is selected as the path column differs.

解决方案

Much info at http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

This has always been an annoying issue in MySQL. There have been ways around it, such as concatenating several fields together (starting with external_id), and then selecting the MAX() of that, and then breaking it back apart.

I suggest you use a derived table. First table (t1) is derived from a simple query where you identify the MAX(external_id), then you join from that to get the rest of the data.

THIS IS ONLY IF external_id IS UNIQUE

SELECT 
   t1.group_id, some_table.id, some_table.mypath
FROM 
   (
      SELECT group_id, MAX(external_id) AS external_id
      FROM some_table
      GROUP BY group_id
   ) as t1
INNER JOIN 
   sometable ON t1.external_id = sometable.external_id
WHERE ...

这篇关于MySQL通过选择组中的最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！