SQL Server语言 - 将外部查询中的值注入子查询(不使用函数)

Question

我有这个查询：

DECLARE @Names VARCHAR(8000) 
SELECT [Number],[ErrorMessage], 
      (SELECT @Names = COALESCE(@Names + ',','') + [Name]
                            FROM [test].[dbo].[b]
                            WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%')
                            )                   
                     as [ErrorHandlingName]
  FROM [test].[dbo].[a]

我的目标是将每个Number发送到子查询中，以便条件WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%')能够使用它。

附言我需要在不用函数包装子查询的情况下做到这一点

有人可以协助吗？

Answer 1

这行得通吗？

SELECT a.[Number],a.[ErrorMessage], 
      (SELECT COALESCE(@Names + ',','') + b.[Name]
                            FROM [test].[dbo].[b] b
                            WHERE b.[ErrorId] like '%' + CONVERT(NVARCHAR(20),a.Number) + '%')
                            )                   
                     as [ErrorHandlingName]
  FROM [test].[dbo].[a] a

Answer 2

SELECT [Number],[ErrorMessage], 
      [ErrorHandlingName] = STUFF(( 
                                SELECT ',' + [Name] AS [text()]
                                FROM [test].[dbo].[b]
                                WHERE [ErrorId] like '%' + CONVERT(NVARCHAR(20),Number) + '%'
                                FOR XML PATH ('')
                            ), 1, 1)                   
  FROM [test].[dbo].[a]