我正在使用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
uj5u.com熱心網友回復：
將查詢與使用:=運算子設定變數的子查詢連接起來。
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()orROW_NUMBER()視窗函式而不是用戶變數。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/474926.html