MySQL Greatest N每组查询挂起 [英] MySQL Greatest N Per Group Query Hangs

问题描述

我有一个包含PredCustId，StartDT和EndDT列的表.对于给定的StartDT，可以有多个PredCustId.这是这样的样子

I have a table with columns PredCustId, StartDT and EndDT. For a given StartDT, there can be multiple PredCustIds. Here's what this looks like

对于每个唯一的StartDT，我想检索具有最大PredCustId的行.我专门尝试实现左连接解决方​​案，如此处所示，但是每次我运行该查询时挂起的查询都没有不明白为什么.

For each unique StartDT, I would like to retrieve the row with the largest PredCustId. I am specifically trying to implement the left-join solution as seen here but the query hangs every time I run it and I don't understand why.

这有效

SELECT a.*
  FROM PredCusts AS a
LEFT OUTER JOIN PredCusts AS b
  ON a.StartDT = b.StartDT;

但是这个挂起了

SELECT a.*
  FROM PredCusts AS a
LEFT OUTER JOIN PredCusts AS b
  ON a.StartDT = b.StartDT AND a.PredCustsId < b.PredCustsId;

为什么?请注意，我正在使用MySQL 5.7.21和MySQL Workbench 6.3.

Why? Note that I am using MySQL 5.7.21 and MySQL Workbench 6.3.

编辑我的表有约370,000行.唯一的索引是主键PredCustsId.

EDIT My table has ~370,000 rows. The only index is the Primary Key, PredCustsId.

推荐答案

您可以在子查询上使用内部联接以获取最大值

You could use a inner join on a subquery for max value

select * from PredCusts p
inner join (
  select StartDT, max(PredCustId) max_precustid
  from PredCusts
  group by StartDT
) t on t.StartDT = p.StartDT and p.PredCustId = t.max_precustid

这篇关于MySQL Greatest N每组查询挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！