【Mysql】分组取得最新值的相关SQL改写

最近项目上遇到个效率很差的SQL，通过沟通和整理，发现SQL中存在大量下面这种SQL的子查询：

(#获取**最新数据
select security_id,
       trade_date,
       reliability,
       modified_duration,
       net_px,
       ytm,
       YEAR_TO_MAT
  from (select security_id,
               trade_date,
               reliability,
               modified_duration,
               net_px,
               ytm,
               YEAR_TO_MAT,
               rank
          from (select a.trade_date,
                       a.security_id,
                       a.reliability,
                       a.modified_duration,
                       a.net_px,
                       a.ytm,
                       a.YEAR_TO_MAT,
                       @rownum := @rownum + 1,
                       if(@security_id = a.security_id,
                          @rank := @rank + 1,
                          @rank := 1) as rank,
                       @security_id := a.security_id
                  from (select security_id,
                               trade_date,
                               reliability,
                               modified_duration,
                               net_px,
                               ytm,
                               YEAR_TO_MAT
                          from ****
                         where QA_ACTIVE_FLG = 1
                         order by trade_date desc, reliability desc) a,
                       (select @rownum := 0, @security_id := null, @rank := 0) b) result) bb
 where bb.rank = 1)

所涉及的表会扫描两遍，后面给的给改写建议如下，不仅SQL逻辑简单清晰了，而且表只要scan一遍了。

#获取**最新数据  
select security_id,
max(trade_date) trade_date,
max(reliability) reliability,
substring_index(GROUP_CONCAT(modified_duration order by trade_date desc, reliability desc),',',1) modified_duration, 
substring_index(GROUP_CONCAT(net_px order by trade_date desc, reliability desc),',',1) net_px,
substring_index(GROUP_CONCAT(ytm order by trade_date desc, reliability desc),',',1) ytm,
substring_index(GROUP_CONCAT(YEAR_TO_MAT order by trade_date desc, reliability desc),',',1) YEAR_TO_MAT      
  from ***
 where QA_ACTIVE_FLG = 1
group by security_id
/*###########################*/

供大家参考。