我可以使用子查询连接字符串吗？

Question

我甚至不知道如何正确地说出这个问题。我正在尝试编写一个查询案例（状态：关闭）的查询，检查特定用户是否输入了一个便笺，如果是，请检查该便笺后面是否添加了便条（该便条会说明案件重新开放或不）。下面是我有：

SELECT DISTINCT scr.CaseId, scr.AssignedSRIdent, s.RepFullName FROM dbo.SupportCaseRoot scr 
INNER JOIN #comparison1 c ON c.CaseId = scr.CaseId 
INNER JOIN #comparison2 c2 ON c2.CaseId = scr.CaseId 
INNER JOIN dbo.servicereproot s ON s.SRIdent = scr.SRIdent 
INNER JOIN dbo.SupportCaseNote scn ON scn.CaseId = scr.CaseId 
WHERE scr.StatusId IN (4, 36, 37, 39, 28) 
AND c.DateCreated > c2.DateCreated 
AND scn.Description LIKE '%status%of%' + ' ' + (SELECT scs.Description FROM dbo.SupportCaseStatus scs WHERE scs.StatusId NOT IN (4, 36, 37, 39, 28)) 
ORDER BY scr.CaseId 

当我运行它，我得到这个错误：

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 

我假设的最后一个AND语句和我在级联愚蠢的尝试，因为。我希望它找到“[找到所有描述]的状态”。任何人有任何建议？

Answer 1

你可以使用一些解决方案：

• 使用“顶部1”或“行偏移”久违的子查询或单个值
  
• 使用聚合函数返回单一（为前像Max。）在子查询或值
  
• 与一个函数返回

Answer 2

这里的误差是在子表返回更个单值替换该子查询一个值。

隔离子查询并查看您获得的结果。子查询最多需要返回一个结果才能“可连接”。

Answer 3

尝试在子查询中相匹配，就像这样：

SELECT DISTINCT scr.CaseId, scr.AssignedSRIdent, s.RepFullName 
FROM dbo.SupportCaseRoot scr 
INNER JOIN #comparison1 c ON c.CaseId = scr.CaseId 
INNER JOIN #comparison2 c2 ON c2.CaseId = scr.CaseId 
INNER JOIN dbo.servicereproot s ON s.SRIdent = scr.SRIdent 
INNER JOIN dbo.SupportCaseNote scn ON scn.CaseId = scr.CaseId 
WHERE scr.StatusId IN (4, 36, 37, 39, 28) 
AND c.DateCreated > c2.DateCreated 
AND EXISTS(
    SELECT 1 FROM dbo.SupportCaseStatus scs WHERE scs.StatusId NOT IN (4, 36, 37, 39, 28) 
    AND scn.Description LIKE ('%status%of%' + ' ' + scs.Description) 
) 
ORDER BY scr.CaseId