用Join替换mysql变量

Question

我使用的是MySQL 5-enterprise-commercial版本。我已经编写了以下2个查询，我想使用join将其转换为单个查询。

SET @row_number :=0;
SELECT
@row_number:= case
when @RId =  r_id then @row_number + 1
else 1
end as rnum,
@RId:=r_id as r_id,
msg,
supplier,
rrtry from (
SELECT
a.r_id as r_id,
mta.message as msg,
tpr.supplier as supplier,
trw.retry as rrtry,
a.createdAt as createdAt,
FROM sa.nra a,
sa.nmta mta,
sa.treq tpr,
sa.twflw trw
WHERE tpr.n_r_id = a.r_id
AND trw.astp = mta.stp
AND mta.rstatus in ('FAIL')
AND tpr.p_w_id = trw.id
AND a.a_mid = mta.id
AND a.createdAt  BETWEEN now() - interval 30 DAY AND now() 
ORDER BY  a.r_id
) as  sa
HAVING rnum = 1
ORDER BY createdAt DESC;

基本上，r_id有多个条目，我想获得特定r_id的最新条目，这就是为什么我使用ORDER BY a_createdAt DESC

Answer 1

将查询与使用:=操作符设置变量的子查询连接。

SELECT
@row_number:= case
when @RId =  r_id then @row_number + 1
else 1
end as rnum,
@RId:=r_id as r_id,
msg,
supplier,
rrtry 
from (
SELECT
a.r_id as r_id,
mta.message as msg,
tpr.supplier as supplier,
trw.retry as rrtry,
a.createdAt as createdAt
FROM sa.nra a
JOIN sa.nmta mta ON a.a_mid = mta.id
JOIN sa.treq tpr ON tpr.n_r_id = a.r_id
JOIN sa.twflw trw ON trw.astp = mta.stp AND tpr.p_w_id = trw.id
WHERE mta.rstatus in ('FAIL')
ORDER BY  a.r_id
) as  sa
CROSS JOIN (SELECT @row_number := 0) AS vars
HAVING rnum = 1
ORDER BY a_createdAt DESC;

我已经用ANSI JOIN替换了子查询中的交叉积。

如果你使用的是MySQL 8.0，你可以使用RANK()或ROW_NUMBER()窗口函数代替用户变量。