SQL 过滤重复申请数据，获取最新记录 - 使用 ROW_NUMBER() 函数

可以使用子查询和ROW_NUMBER()函数实现过滤重复并获取最新的一条申请数据，同时保证Query的查询不能被改动，例如：

SELECT * FROM (
    SELECT a.id,b.id AS seaservice_id,a.create_time,b.talent_user_id,b.company_id,b.title,b.pay_amount,b.payment_way,b.currency,b.completion_time,b.sea_service_status,b.service_address,
    COALESCE(c.short_name_cn,e.realname, e.nickname, e.username) AS companyName,a.status AS applyStatus,IFNULL(d.has_withdraw,0) as hasWithdraw,
    ROW_NUMBER() OVER (PARTITION BY b.id ORDER BY a.create_time DESC) AS rn
    FROM iw_seaservice_apply a 
    LEFT JOIN iw_seaservice b ON a.seaservice_id = b.id 
    LEFT JOIN iw_company c ON b.company_id = c.id
    LEFT JOIN iw_seaservice_completion_apply d ON a.id = d.seaservice_apply_id 
    LEFT JOIN iw_talent_user e ON b.talent_user_id = e.id
) T 
WHERE T.rn = 1 ${ew.customSqlSegment}
<if test="minPayAmount != null">
    AND pay_amount &gt;= #{minPayAmount}
</if>
<if test="maxPayAmount != null">
    AND pay_amount &lt;= #{maxPayAmount}
</if>
<if test="applyStatus != null">
    AND applyStatus = #{applyStatus}
</if>
<if test="companyName != null and companyName != '' ">
    AND companyName LIKE '${companyName}%'
</if>
ORDER BY create_time DESC

在子查询中，使用ROW_NUMBER()函数对每个seaservice_id分组，按申请时间倒序排列，得到每个分组中最新的一条申请数据，并标记其行号为1。然后在外层SELECT中过滤掉行号不为1的数据，即可得到过滤重复并获取最新的一条申请数据的结果。