相关敏感表信息已经脱敏处理

1.待转换 sqlserver SQL

select
a.party_id,
a.publish_date,
a.rating_date,
replace(replace(replace(rating,'p',''),' ',''),'i','') rating2,
lead(replace(replace(replace(rating,'p',''),' ',''),'i',''),1,0) 
over (partition by party_id,RAT_COM_ID,CREDIT_TYPE_CD order by PUBLISH_DATE desc) rating1
from 
testtable a
where a.QA_ACTIVE_FLG=1
and party_id=2 and RAT_COM_ID=15182 and CREDIT_TYPE_CD='ISS_RATING_L'

2.mysql 改写方法一

通过分组排序来做，需要在外面再嵌套一层
通过分组来做 mysql视图不支持变量和子查询，查询开销较大，只做思路提示

select
case when (ifnull(@party_id,0)<>ifnull(party_id,0) 
     or ifnull(@RAT_COM_ID,0)<>ifnull(RAT_COM_ID,0)
     or ifnull(@CREDIT_TYPE_CD,'0')<>ifnull(CREDIT_TYPE_CD,'0'))
then @rownum:=1 else @rownum:=@rownum+1 end rownum,
@party_id:=a.party_id party_id,
@RAT_COM_ID:=a.RAT_COM_ID RAT_COM_ID,
@CREDIT_TYPE_CD:=CREDIT_TYPE_CD CREDIT_TYPE_CD,
a.publish_date,
a.rating_date,
replace(replace(replace(rating,'p',''),' ',''),'i','') rating2
from 
(SELECT @rownum:=0) r ,
(select @party_id:=0,@RAT_COM_ID:=0,@CREDIT_TYPE_CD:='' ) n,
testtable a
where a.QA_ACTIVE_FLG=1
and party_id=2 and RAT_COM_ID=15182 and CREDIT_TYPE_CD='ISS_RATING_L'
order by party_id,RAT_COM_ID,CREDIT_TYPE_CD,PUBLISH_DATE desc

3. 改写方法2（高效，推荐）

我们明明离开的那么近，怎么就是捅不破那层纸呢？
真的咫尺天涯？未必。

select
case when (ifnull(@party_id,0)<>ifnull(party_id,0) /*分组字段判断*/
     or ifnull(@RAT_COM_ID,0)<>ifnull(RAT_COM_ID,0)
     or ifnull(@CREDIT_TYPE_CD,'0')<>ifnull(CREDIT_TYPE_CD,'0'))
then @rating1:='0' else @rating1 end rating1,
@party_id:=a.party_id party_id /*分组字段赋值*/,
a.publish_date,
a.rating_date,
@RAT_COM_ID:=a.RAT_COM_ID RAT_COM_ID/*分组字段赋值*/,
@CREDIT_TYPE_CD:=CREDIT_TYPE_CD CREDIT_TYPE_CD/*分组字段赋值*/,
/*结果字段赋值*/
@rating1:=replace(replace(replace(rating,'p',''),' ',''),'i','') rating2 /*上面的家伙快下来*/
from 
(select @party_id:=0,@RAT_COM_ID:=0,@CREDIT_TYPE_CD:='',@rating1:='0' ) n,
testtable a /*原表要加别名*/
where a.QA_ACTIVE_FLG=1
and party_id=2 and RAT_COM_ID=15182 and CREDIT_TYPE_CD='ISS_RATING_L'
order by party_id ,RAT_COM_ID,CREDIT_TYPE_CD,/*分组字段在前*/PUBLISH_DATE asc