带有左连接的前1位

给定下面的查询，dps _ marks 中可能有多个行具有相同的标记键，但是我们只希望针对第一个标记键进行连接。如果我使用这个查询并删除 top 1和 ORDER BY，我会得到一个 mbg.mark_ value 的值，但是按原样运行，它总是返回 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'
156977 次浏览
小开

因为来自排序子查询的 TOP 1没有 profile_id = 'u162231993' 删除 where u.id = 'u162231993'，然后看到结果。

分别运行子查询以了解发生了什么。

小开

达米尔说得对,

您的子查询需要确保 dps _ user。Id 等于 um.profile _ id，否则它将获取顶行，这可能，但可能不等于您的 id‘ u162231993’

您的查询应该如下所示:

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'
WHERE u.id = um.profile_id
ORDER BY m.creation_date
) MBG ON MBG.profile_id=u.id
WHERE u.id = 'u162231993'
小开

调试这类情况的关键是独立运行子查询/内联视图，以查看输出是什么:

  SELECT TOP 1
dm.marker_value,
dum.profile_id
FROM DPS_USR_MARKERS dum (NOLOCK)
JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id
AND dm.marker_key = 'moneyBackGuaranteeLength'
ORDER BY dm.creation_date

Running that, you would see that the profile_id value didn't match the u.id value of u162231993, which would explain why any mbg references would return null (thanks to the left join; you wouldn't get anything if it were an inner join).

您已经使用 TOP将自己编码到了一个角落，因为如果您想为其他用户运行查询，那么现在必须调整查询。更好的办法是:

   SELECT u.id,
x.marker_value
FROM DPS_USER u
LEFT JOIN (SELECT dum.profile_id,
dm.marker_value,
dm.creation_date
FROM DPS_USR_MARKERS dum (NOLOCK)
JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id
AND dm.marker_key = 'moneyBackGuaranteeLength'
) x ON x.profile_id = u.id
JOIN (SELECT dum.profile_id,
MAX(dm.creation_date) 'max_create_date'
FROM DPS_USR_MARKERS dum (NOLOCK)
JOIN DPS_MARKERS dm (NOLOCK) ON dm.marker_id= dum.marker_id
AND dm.marker_key = 'moneyBackGuaranteeLength'
GROUP BY dum.profile_id) y ON y.profile_id = x.profile_id
AND y.max_create_date = x.creation_date
WHERE u.id = 'u162231993'

这样，您就可以更改 where子句中的 id值，以检查系统中任何用户的记录。

小开
最佳答案

使用 OUTER APPY 代替 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 不同，APPY 允许您在内部查询中引用 uid。


提交答案