mysql-按组类型连接第一条记录和最后一条记录? [英] mysql - join first and last records by group type?

问题描述

使用如下表:

idx type dat
0   a    foo1
1   b    foo2
2   c    foo3
3   a    foo4
4   b    foo5
5   c    foo6
6   a    foo7
7   b    foo8
8   c    foo9

如何获取每种类型的第一个和最后一个数据:

How can i get the first and last data of each type:

例如:

a foo1 foo7
b foo2 foo8
c foo3 foo9

我已经尝试过此查询，但是它的速度变慢，即使使用idx和类型上的索引也是如此:

I have tried this query, but its way to slow, even with indexes on idx and type:

select mins.type, mins.dat, mytable.dat from mytable

--get the maximums
inner join
 (select max(idx) as maxidx from mytable group by type) as a
on a.maxidx = mytable.idx


--join the maximums to the minimums
inner join

--get the minimums
 (select * from mytable inner join (select min(idx) as minidx from mytable group by type) as b 
on b.minidx = mytable.idx ) as mins

on issues.type = mins.type

on issues.type = mins.type的加入似乎正在使它放慢很多，因为mins.type是派生的而不是未索引的?

The join of on issues.type = mins.type seems to be what is slowing it down so much because mins.type is derived and not indexed?

推荐答案

使用 GROUP BY 的mysql的"funky"功能，而无需汇总其他列，该列仅返回该组的 first 行.然后，问题就变成了使用此功能(通常通过使用别名查询)使行在之前正确排列.

Use mysql's "funky" functionality of a GROUP BY without aggregating the other columns, which simply returns the first row of the group. The problem then becomes getting the rows into the correct order before using this functionality, typically by using an aliased query.

这种方法避免了任何相关的子查询(每行查询)，并且只需要在表上进行两次遍历(每个排序方向一次):

This approach avoids any correlated subqueries (queries per row) and needs just two passes over the table (one for each direction of ordering):

select x2.type, x2.dat as first_dat, y2.dat as last_dat
from (select *
  from (select type, dat
    from so8735514
    order by 1, 2) x1
  group by 1) x2
join (select *
  from (select type, dat
    from so8735514
    order by 1, 2 desc) y1
  group by 1) y2 on y2.type = x2.type;

测试代码:

create table so8735514 (idx int, type text, dat text);
insert into so8735514 values
(0, 'a', 'foo1'),
(1, 'b', 'foo2'),
(2, 'c', 'foo3'),
(3, 'a', 'foo4'),
(4, 'b', 'foo5'),
(5, 'c', 'foo6'),
(6, 'a', 'foo7'),
(7, 'b', 'foo8'),
(8, 'c', 'foo9');

输出:

+------+-----------+----------+
| type | first_dat | last_dat |
+------+-----------+----------+
| a    | foo1      | foo7     |
| b    | foo2      | foo8     |
| c    | foo3      | foo9     |
+------+-----------+----------+

这篇关于mysql-按组类型连接第一条记录和最后一条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！