mysql - 在mysql中获取具有最大列值的行

Question

我一直在为此苦苦挣扎。我尝试了所有方法，如左外连接、分组甚至子查询，但都没有成功。这是我的查询。

select star_ident,transition_ident,fix_ident,sequence_num,route_type
from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU';  

我需要从上面的结果集中提取那些对于给定的 transition_ident 和 star_ident 具有最大 sequence_num 的行。这是我的查询。

select star_ident,transition_ident,fix_ident,max(sequence_num)
from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU'
group by transition_ident,star_ident;

但是上面的查询产生了错误的结果。我什至尝试过连接。

select yt1.star_ident,yt1.transition_ident,yt1.fix_ident
from corept.std_star_leg yt1
left outer join corept.std_star_leg yt2
on (yt1.star_ident = yt2.star_ident and yt1.transition_ident=yt2.transition_ident and      yt1.sequence_num < yt2.sequence_num)
where yt1.airport_ident='KMMU' and yt1.data_supplier='J'
and yt2.airport_ident='KMMU' and yt2.data_supplier='J' and yt2.star_ident is null;

但我最终得到了零行。请提供一种有效的方法来执行此操作。我需要针对 13K 条目运行此查询。谢谢。

Answer 1

您的选择中有非聚合列，它们不属于分组依据。

来自<强> MYSQL DOCS

MySQL extends the use of `GROUP BY` so that the select list can refer to nonaggregated
columns not named in the GROUP BY clause. You can use this feature to get better
performance by avoiding unnecessary column sorting and grouping. However, this is 
useful primarily when all values in each nonaggregated column not named in the 
GROUP BY are the same for each group. The server is free to choose any value from 
each group, so unless they are the same, the values chosen are indeterminate.

因此，为了获得正确的结果，请将所有 select 列添加到 group by

编辑

select b.* 
from 
corept.std_star_leg b
inner join
(select star_ident,transition_ident,max(sequence_num) as seq
from corept.std_star_leg where data_supplier='J' and airport_ident='KMMU'
group by star_ident,transition_ident) a
on b.star_ident = a.star_ident and a.transition_ident = b.transition_ident and
b.sequence_num = a.seq;  

希望对你有帮助....