T-SQL如何在LEFT OUTER JOIN子查询中引用主SELECT的字段值

你碰到的这个绑定报错，核心原因是LEFT OUTER JOIN后面的子查询（派生表）是独立作用域，无法直接引用外层主查询里EFP_MessageCenter表的字段，所以你直接在子查询的WHERE里写关联外层字段的条件就会触发报错。

有两种常用的解决方案：

方案1：使用OUTER APPLY替代LEFT JOIN（推荐，改动最小） #

APPLY运算符是T-SQL专门用于需要在子查询/表值函数里引用外层表字段的场景，逻辑和你原来的写法完全一致，只需要少量改动：

SELECT LOWER(EFP_MessageCenter.MessageSender) AS MessageSenderInitials
, MAX(SenderInfo.FullName) AS SenderFullName
, MAX(SenderInfo.ProfilePicture) AS SenderProfilePicture
, MAX(EFP_MessageCenter_Receiver.UserID) AS ReceiverID
, MAX(EFP_MessageCenter.MessageTimestamp) AS ChangeDate
, COUNT(DisplayCountSelect.Displayed) AS CountNonReadMessages
FROM EFP_MessageCenter_Receiver
INNER JOIN EFP_MessageCenter ON EFP_MessageCenter_Receiver.MessageID = EFP_MessageCenter.id 
INNER JOIN EFP_EmploymentUser AS SenderInfo ON EFP_MessageCenter.MessageSender = SenderInfo.Initials 
-- 把LEFT OUTER JOIN改成OUTER APPLY
OUTER APPLY
    (SELECT EFP_MessageCenter_Receiver_1.Displayed, EFP_MessageCenter_Receiver_1.UserID, EFP_MessageCenter_1.MessageSender
    FROM EFP_MessageCenter AS EFP_MessageCenter_1 
    INNER JOIN EFP_MessageCenter_Receiver AS EFP_MessageCenter_Receiver_1 ON EFP_MessageCenter_1.id = EFP_MessageCenter_Receiver_1.MessageID
    -- 这里直接引用外层的EFP_MessageCenter.MessageSender即可
    WHERE (EFP_MessageCenter_Receiver_1.Displayed = 0) AND (EFP_MessageCenter_Receiver_1.UserID = 65) AND (EFP_MessageCenter_1.MessageSender = EFP_MessageCenter.MessageSender)) 
    AS DisplayCountSelect 
    ON DisplayCountSelect.UserID = EFP_MessageCenter_Receiver.UserID
WHERE (EFP_MessageCenter_Receiver.UserID = 65) AND (EFP_MessageCenter.MessageType = 'SPECIFIC')
GROUP BY EFP_MessageCenter.MessageSender
ORDER BY ChangeDate DESC

方案2：将发送人匹配条件移到JOIN的关联子句中 #

如果你不想用APPLY，也可以把子查询里的发送人过滤条件去掉，放到外层的JOIN关联条件里：

SELECT LOWER(EFP_MessageCenter.MessageSender) AS MessageSenderInitials
, MAX(SenderInfo.FullName) AS SenderFullName
, MAX(SenderInfo.ProfilePicture) AS SenderProfilePicture
, MAX(EFP_MessageCenter_Receiver.UserID) AS ReceiverID
, MAX(EFP_MessageCenter.MessageTimestamp) AS ChangeDate
, COUNT(DisplayCountSelect.Displayed) AS CountNonReadMessages
FROM EFP_MessageCenter_Receiver
INNER JOIN EFP_MessageCenter ON EFP_MessageCenter_Receiver.MessageID = EFP_MessageCenter.id 
INNER JOIN EFP_EmploymentUser AS SenderInfo ON EFP_MessageCenter.MessageSender = SenderInfo.Initials 
LEFT OUTER JOIN 
    (SELECT EFP_MessageCenter_Receiver_1.Displayed, EFP_MessageCenter_Receiver_1.UserID, EFP_MessageCenter_1.MessageSender
    FROM EFP_MessageCenter AS EFP_MessageCenter_1 
    INNER JOIN EFP_MessageCenter_Receiver AS EFP_MessageCenter_Receiver_1 ON EFP_MessageCenter_1.id = EFP_MessageCenter_Receiver_1.MessageID
    -- 删掉原来的发送人过滤条件
    WHERE (EFP_MessageCenter_Receiver_1.Displayed = 0) AND (EFP_MessageCenter_Receiver_1.UserID = 65)) 
    AS DisplayCountSelect 
    -- 关联的时候加发送人匹配条件
    ON DisplayCountSelect.UserID = EFP_MessageCenter_Receiver.UserID AND DisplayCountSelect.MessageSender = EFP_MessageCenter.MessageSender
WHERE (EFP_MessageCenter_Receiver.UserID = 65) AND (EFP_MessageCenter.MessageType = 'SPECIFIC')
GROUP BY EFP_MessageCenter.MessageSender
ORDER BY ChangeDate DESC

两种方案的执行结果完全一致，都可以实现按每个发送人统计对应用户未读消息数的需求。

内容的提问来源于stack exchange，提问作者Stig Kølbæk