代码之家 › 专栏 › 技术社区 › dstarh

左连接的前1个

outer-join sql-server

dstarh · 技术社区 · 16 年前

考虑到下面的查询,DPS标记中可能有多行具有相同的标记键,但我们只希望针对第一行进行联接。如果我接受这个查询并删除top 1并按order by获取mbg.marker_value的值,但按原样运行,它始终返回空值。

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'

4 回复 | 直到 12 年前

161

Remus Rusanu 16 年前

使用外部应用而不是左联接:

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。

OMG Ponies 16 年前

调试这种情况的关键是自己运行子查询/内联视图,以查看输出是什么:

  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

运行它,你会看到 profile_id 值与 u.id 价值 u162231993 这就解释了为什么 mbg 引用将返回 null (多亏了左边的连接,如果是内部连接,你什么也得不到)。

你已经用 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'

有了它,你可以改变 id 价值在 where 子句检查系统中任何用户的记录。

Damir Sudarevic 16 年前

因为 TOP 1 从已排序的子查询没有 profile_id = 'u162231993' 去除 where u.id = 'u162231993' 然后看看结果。

单独运行子查询以了解发生了什么。

Nathan Koop 16 年前

达米尔是对的,

您的子查询需要确保dps_user.id等于um.profile_id,否则它将获取可能但可能不等于您的“u162231993”id的顶行。

您的查询应如下所示:

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'