SQL中Cross-Apply()的替代方法

Question

下面的SQL查询如何使用JOIN编写？我正在尝试将SQL脚本转换为Trino/Presto，但这不支持Cross-Apply((。

select i.*,k.*
FROM dbo.[LN] i
CROSS APPLY(select top 1 * from
dbo.[LN]  j
where i.vnd_nbr = j.vnd_nbr and i.cltstyle = j.cltstyle and j.compdate > i.compdate order by compdate
) k order by i.VND_NBR

Answer 1

因为您的系统不支持CROSS APPLY

xou可以使用CTE进行这种purpüose

WITH CTE AS(
select i.*,k.*,
ROW_NUMBBER() OVER (PARTITION BY k.vnd_nbr,k.cltstyle, k.compdate ORDER BY k.compdate) en
FROM dbo.[LN] i
JOIN 
dbo.[LN]  k
ON i.vnd_nbr = k.vnd_nbr and i.cltstyle = k.cltstyle and k.compdate > i.compdate
)  
SELECT * FROM 
CTE
WHERE rn = 1
order by VND_NBR