获取每组具有最高/最小<whatever>的记录-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文

获取每组具有最高/最小<whatever>的记录

保持可爱mmm 2020-05-11 11:01:02 16

怎么做?

该问题的原标题是“ 在带有子查询的复杂查询中使用等级(@Rank:= @Rank + 1)-可以吗? ”,因为我一直在寻找使用等级的解决方案,但是现在我看到Bill所发布的解决方案是好多了。

原始问题:

我正在尝试组成一个查询,该查询将从给定定义顺序的每个组中获取最后一条记录:

SET @Rank=0;

select s.* from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as t group by GroupId) as t join ( select *, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as s on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField 表达式@Rank := @Rank + 1通常用于等级,但对我来说,在2个子查询中使用时,它看起来可疑,但仅初始化一次。这样行吗?

其次,它将与一个被多次评估的子查询一起使用吗?像where(或having)子句中的子查询(另一种写上面的方法):

SET @Rank=0;

select Table.*, @Rank := @Rank + 1 AS Rank from Table having Rank = (select max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table as t0 order by OrderField ) as t where t.GroupId = table.GroupId ) order by OrderField 提前致谢!

分享到
取消 提交回答
全部回答(1)
  • 保持可爱mmm
    2020-05-11 11:01:18

    因此,您想获得OrderField每组最高的行吗?我会这样:

    SELECT t1.* FROM Table AS t1 LEFT OUTER JOIN Table AS t2 ON t1.GroupId = t2.GroupId AND t1.OrderField < t2.OrderField WHERE t2.GroupId IS NULL ORDER BY t1.OrderField; // not needed! (note by Tomas) (Tomas EDIT:如果同一组中有更多具有相同OrderField的记录,而您恰好需要其中之一,则可能需要扩展条件:

    SELECT t1.* FROM Table AS t1 LEFT OUTER JOIN Table AS t2 ON t1.GroupId = t2.GroupId AND (t1.OrderField < t2.OrderField OR (t1.OrderField = t2.OrderField AND t1.Id < t2.Id)) WHERE t2.GroupId IS NULL 编辑结束。)

    换句话说,以相同或更大的值返回t1没有其他行t2存在的行。当为NULL时,表示左外部联接未找到此类匹配项,因此在组中具有最大值。GroupIdOrderFieldt2.*t1OrderField

    没有等级,没有子查询。如果您在上拥有复合索引,这应该可以快速运行并使用“使用索引”优化对t2的访问(GroupId, OrderField)。

    关于性能,请参阅我对检索每个组中的最后一个记录的回答。我尝试了使用堆栈溢出数据转储的子查询方法和联接方法。区别非常明显:在我的测试中,join方法的运行速度提高了278倍。

    重要的是您必须具有正确的索引以获得最佳结果!

    关于使用@Rank变量的方法,它不能像您编写的那样起作用,因为在查询处理完第一个表之后,@ Rank的值不会重置为零。我给你看一个例子。

    我插入了一些虚拟数据,其中一个额外的字段为null,但在我们知道每组最大的行上除外:

    select * from Table;

    +---------+------------+------+ | GroupId | OrderField | foo | +---------+------------+------+ | 10 | 10 | NULL | | 10 | 20 | NULL | | 10 | 30 | foo | | 20 | 40 | NULL | | 20 | 50 | NULL | | 20 | 60 | foo | +---------+------------+------+ 我们可以证明,第一组的排名增加到三,第二组的排名增加到六,并且内部查询正确地返回了这些:

    select GroupId, max(Rank) AS MaxRank from ( select GroupId, @Rank := @Rank + 1 AS Rank from Table order by OrderField) as t group by GroupId

    +---------+---------+ | GroupId | MaxRank | +---------+---------+ | 10 | 3 | | 20 | 6 | +---------+---------+ 现在,在没有连接条件的情况下运行查询,以强制所有行的笛卡尔积,并且我们还获取所有列:

    select s., t. from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as t group by GroupId) as t join ( select *, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as s -- on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField;

    +---------+---------+---------+------------+------+------+ | GroupId | MaxRank | GroupId | OrderField | foo | Rank | +---------+---------+---------+------------+------+------+ | 10 | 3 | 10 | 10 | NULL | 7 | | 20 | 6 | 10 | 10 | NULL | 7 | | 10 | 3 | 10 | 20 | NULL | 8 | | 20 | 6 | 10 | 20 | NULL | 8 | | 20 | 6 | 10 | 30 | foo | 9 | | 10 | 3 | 10 | 30 | foo | 9 | | 10 | 3 | 20 | 40 | NULL | 10 | | 20 | 6 | 20 | 40 | NULL | 10 | | 10 | 3 | 20 | 50 | NULL | 11 | | 20 | 6 | 20 | 50 | NULL | 11 | | 20 | 6 | 20 | 60 | foo | 12 | | 10 | 3 | 20 | 60 | foo | 12 | +---------+---------+---------+------------+------+------+ 从上面我们可以看到每组的最大排名是正确的,但是@Rank在处理第二个派生表时继续增加,直到7或更高。因此,第二个派生表中的等级根本不会与第一个派生表中的等级完全重叠。

    您必须添加另一个派生表,以在处理两个表之间强制@Rank重置为零(并希望优化器不要更改其评估表的顺序,或者使用STRAIGHT_JOIN来防止这种情况):

    select s.* from (select GroupId, max(Rank) AS MaxRank from (select GroupId, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as t group by GroupId) as t join (select @Rank := 0) r -- RESET @Rank TO ZERO HERE join ( select *, @Rank := @Rank + 1 AS Rank from Table order by OrderField ) as s on t.GroupId = s.GroupId and t.MaxRank = s.Rank order by OrderField;

    +---------+------------+------+------+ | GroupId | OrderField | foo | Rank | +---------+------------+------+------+ | 10 | 30 | foo | 3 | | 20 | 60 | foo | 6 | +---------+------------+------+------+ 但是,此查询的优化很糟糕。它不能使用任何索引,它会创建两个临时表,对它们进行困难的排序,甚至使用联接缓冲区,因为它在联接临时表时也无法使用索引。这是来自EXPLAIN以下示例的输出:

    +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ | 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 2 | | | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using join buffer | | 5 | DERIVED | Table | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | | 4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | | 2 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 6 | Using temporary; Using filesort | | 3 | DERIVED | Table | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | +----+-------------+------------+--------+---------------+------+---------+------+------+---------------------------------+ 而我使用左外部联接的解决方案优化得更好。它不使用临时表,甚至不使用报告"Using index",这意味着它可以仅使用索引来解决联接,而无需处理数据。

    +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 6 | Using filesort | | 1 | SIMPLE | t2 | ref | GroupId | GroupId | 5 | test.t1.GroupId | 1 | Using where; Using index | +----+-------------+-------+------+---------------+---------+---------+-----------------+------+--------------------------+ 您可能会读到人们在其博客上宣称“加入会使SQL变慢”的说法,但这是无稽之谈。最差的优化会使SQL变慢。来源:stack overflow

    0 0
云计算
使用钉钉扫一扫加入圈子
+ 订阅

时时分享云计算技术内容,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。

推荐文章