mysql给我的愚人节礼物——分组排序进行排名语句存在的问题

今天我被mysql给愚了，写一个分数排名的业务sql语句，数据库使用的是mysql，数据库版本是8.0.18。在网上找到一个关于排名的语句写的比较复杂，经过调整之后满足了自己的业务需要。在使用过程中出现问题。
使用IF(path=@p1 AND zydh1=@p2,@r:=@r+1,@r:=1)此种方式问题，每次新建连接到数据库时首次查询排序伪劣字段为空，不能进行排名困扰好久。

SELECT A.*,IF(path=@p1 AND zydh1=@p2,@r:=@r+1,@r:=1) AS rownum,@p1:=path,@p2:=zydh1  FROM (
SELECT path,title,tj_zf,zyzytj,zydh1,zydh2,zydh3,zydh4,zydh5,year,lqtype,'0' flag FROM (SELECT * FROM  gk_school_bk_rsinfo WHERE 1=1  AND YEAR=2021 AND PATH IN('3B764') and zydh1 in('20','26','27','29') AND LQTYPE = '普通理科'  AND tj_zf>=450.0 ) A   ) A,(SELECT @p1:=NULL,@p2:=NULL,@rownum:=0) r WHERE 1=1  AND YEAR=2021 AND PATH IN('3B764') and zydh1 in('20','26','27','29') AND LQTYPE = '普通理科'  AND tj_zf>=450.0 order by path,zydh1,tj_zf desc

因此在查找网上资料时改为使用内置函数排序函数row_number()配合分组函数 partition by 进行排序

SELECT A.*  FROM (
SELECT path,title,tj_zf,zyzytj,zydh1,zydh2,zydh3,zydh4,zydh5,year,lqtype,'0' flag,row_number() OVER (partition by path,zydh1 ORDER BY tj_zf DESC) AS 'rank' FROM (SELECT * FROM  gk_school_bk_rsinfo WHERE 1=1  AND YEAR=2021 AND PATH IN('3B764') and zydh1 in('20','26','27','29') AND LQTYPE = '普通理科'  AND tj_zf>=450.0 ) A    ) A WHERE 1=1  AND YEAR=2021 AND PATH IN('3B764') and zydh1 in('20','26','27','29') AND LQTYPE = '普通理科'  AND tj_zf>=450.0 order by path,zydh1,tj_zf desc