sql - 子查询 - 如何引用外部查询值

Question

下面的查询工作正常:

SELECT 
   tblCase.ID AS CaseID, tblCase.UserID AS MyCasesFilter, 
   tblGroupMembership.UserID AS GroupShareFilter, 
   tblDirectCaseSharing.ReceiverUserID AS DirectShareFilter, tblCase.EntryDate, 
   tblUser.LastName AS CaseAuthor, tblCase.Name AS CaseName, 
   COUNT(DISTINCT tblCaseImage.ID) AS TotalImages, 
   tblCaseType.Name AS CaseType, tblCase.SiteName, tblCase.Category, 
   tblCase.FollowUpDateTime, tblCase.Notes
FROM     
   tblDirectCaseSharing 
RIGHT OUTER JOIN
   tblCase 
INNER JOIN tblUser ON tblCase.UserID = tblUser.ID ON tblDirectCaseSharing.CaseID = tblCase.ID 
LEFT OUTER JOIN
   tblGroupMembership 
INNER JOIN
   tblGroupCase ON tblGroupMembership.GroupID = tblGroupCase.GroupID ON tblCase.ID = tblGroupCase.CaseID 
LEFT OUTER JOIN
   tblCaseType ON tblCase.CaseTypeID = tblCaseType.ID 
LEFT OUTER JOIN
   tblCaseImage ON tblCase.ID = tblCaseImage.CaseID
GROUP BY 
   tblCase.ID, tblCaseType.Name, tblCase.SiteName, tblCase.EntryDate, 
   tblCase.Category, tblCase.FollowUpDateTime, tblCase.Notes, tblCase.UserID, 
   tblGroupMembership.UserID, tblDirectCaseSharing.ReceiverUserID, 
   tblUser.LastName, tblCase.Name
HAVING 
   (tblCase.UserID = 1) 
   AND (tblGroupMembership.UserID = 2) 
   AND (tblDirectCaseSharing.ReceiverUserID = 3)
ORDER BY 
   tblCase.EntryDate DESC

我想使用选择子查询向上述结果添加一个额外的选择列:

STUFF((
SELECT ', ' +tblGroup.Name  as [text()] 
FROM     tblCase INNER JOIN
                  tblGroupCase ON tblCase.ID = tblGroupCase.CaseID INNER JOIN
                  tblGroup ON tblGroupCase.GroupID = tblGroup.ID
WHERE tblCase.ID = ***
FOR XML PATH('')
),1,2,'')
AS ConcatGroupShares

select 子查询有一个 where 子句，我需要获取 tblcase.id，它是结果集的第一列。如何在子查询中引用该值。

Answer 1

在主查询中输入别名

SELECT 
   tcase.ID AS CaseID, tblCase.UserID AS MyCasesFilter, 
   tblGroupMembership.UserID AS GroupShareFilter, 
   tblDirectCaseSharing.ReceiverUserID AS DirectShareFilter, tblCase.EntryDate, 
   tblUser.LastName AS CaseAuthor, tblCase.Name AS CaseName, 
   COUNT(DISTINCT tblCaseImage.ID) AS TotalImages, 
   tblCaseType.Name AS CaseType, tblCase.SiteName, tblCase.Category, 
   tblCase.FollowUpDateTime, tblCase.Notes
 FROM     
   tblDirectCaseSharing 
 RIGHT OUTER JOIN
   tblCase As tcase                       <=====

并在子查询中使用此别名:

 STUFF((
SELECT ', ' +tblGroup.Name  as [text()] 
FROM     tblCase INNER JOIN
                  tblGroupCase ON tblCase.ID = tblGroupCase.CaseID INNER JOIN
                  tblGroup ON tblGroupCase.GroupID = tblGroup.ID
 WHERE tblCase.ID = tcase.id
 FOR XML PATH('')
 ),1,2,'')
 AS ConcatGroupShares

有关相关子查询的更多信息:

http://en.wikipedia.org/wiki/Correlated_subquery