编码小技巧-3

MySQL中类似SQLServer中OUTER APPLY的语法
注意MySQL的版本需要8.0.14及以上

使用INNER JOIN方法如下

SELECT * FROM TABLE_NAME t1
INNER JOIN LATERAL (SELECT t2.* FROM TABLE_NAME t2 WHERE t1.xx = t2.xx ORDER BY t2.xx DESC LIMIT 1) t2;

使用LEFT JOIN方法如下

SELECT * FROM TABLE_NAME t1
LEFT JOIN LATERAL (SELECT t2.* FROM TABLE_NAME t2 WHERE t1.xx = t2.xx ORDER BY t2.xx DESC LIMIT 1) t2 ON TRUE;

查看MySQL的版本

SELECT VERSION();