问题描述
我试图在一个包含约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
这在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通过选择组中的最大记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!