具有左连接的前 1 个 [英] Top 1 with a left join

问题描述

鉴于下面的查询，dps_markers 中可能有多行具有相同的标记键，但我们只想加入第一行.如果我接受这个查询并删除前 1 个和 ORDER BY，我会得到一个 mbg.marker_value 的值，但按原样运行它总是返回 null

Given the query below there might be multiple rows in dps_markers with the same marker key but we only want to join against the first. If I take this query and remove the top 1 and ORDER BY I get a value for mbg.marker_value but run as it is it always returns null

SELECT u.id, mbg.marker_value 
FROM dps_user u
LEFT JOIN 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     ORDER BY m.creation_date
    ) MBG ON MBG.profile_id=u.id 
WHERE u.id = 'u162231993'

推荐答案

使用 OUTER APPLY 而不是 LEFT JOIN:

Use OUTER APPLY instead of LEFT JOIN:

SELECT u.id, mbg.marker_value 
FROM dps_user u
OUTER APPLY 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     WHERE um.profile_id=u.id 
     ORDER BY m.creation_date
    ) AS MBG
WHERE u.id = 'u162231993';

与 JOIN 不同，APPLY 允许您在内部查询中引用 u.id.

Unlike JOIN, APPLY allows you to reference the u.id inside the inner query.

这篇关于具有左连接的前 1 个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！