Teradata到mysql的转换--资格和排名

Question

我有一个teradata代码，如下所示：

select *
from date_dim a
left join 
(select incident_number,incident_type,submit_date from itamr qualify RANK() OVER (PARTITION BY incident_number ORDER BY last_modified_date DESC) = 1)b
on a.Clndr_Dt = b.submit_date
and b.incident_type = 'Request';

我需要将合格等级和分区转换为mysql。我尝试分组而不是分区。有人能帮我解决如何在mysql中转换qualify和排名吗？

Answer 1

QUALIFY是一个teradata专有关键字。Howover RANK函数适用于任何关系型数据库。您可以用子查询替换QUALIFY语句，如下所示：

 select *
from date_dim a
left join 
(
select * from 
(select incident_number,incident_type,submit_date ,
RANK() OVER (PARTITION BY incident_number ORDER BY last_modified_date DESC) rn
from itamr ) sub where rn = 1 ) b
on a.Clndr_Dt = b.submit_date
and b.incident_type = 'Request';