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

来源：互联网发布：mac os 10.12 beta5 编辑：程序博客网时间：2024/05/22 11:54

最近项目上遇到个效率很差的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 = 1group by security_id/*###########################*/

供大家参考。

0 0