项目场景:
在MySQL 8.0.28使用row_number() over(partition by ) 没有问题,但是在线上使用发现问题。
问题描述
- 线上MySQL 版本
- 我的SQL
select t.* from (
select *,
row_number() over (partition by client_name,client_source,salesman order by reg_date desc) rw
from ws_develop_clue) t
where t.rw = 1
- 在低版本MySQL执行错误如下
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by client_name,client_source,salesman order by reg_date desc) rw from' at line 2
原因分析:
线上MySQL 5.7.35-log 版本不支持开窗函数
解决方案:
升级MySQL版本成本太高,目前剩下修改SQL的方法。
select z.client_name,z.client_source,z.salesman,z.reg_date,z.rank from(
select x.*,@rownum:=@rownum+1,if(@part=x.client_name,@r:=@r+1,@r:=1) as rank,@part:=x.client_name from (
select reg_date,client_name,client_source,salesman from ws_develop_clue e
order by e.client_name,e.reg_date desc
) x,(select @rownum:=0,@part:=null,@r:=0) rt
)z where z.rank =1