递归CTE查询联接到第二个表的帮助

Question

我的目标是遍历表tbl，并在遍历该表的同时从另一个表tbl2中选择一个国家缩写（如果存在），并将这些结果附加到最终输出中。

我将使用的示例来自这篇文章

tbl2有一个外键’tbl_id’来tbl，看起来像这样

INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
VALUES 
 (100, 'EU', 1) 
,(101, 'AS', 2) 
,(102, 'DE', 3) 
,(103, 'CN', 5)

*注意：并非所有国家/地区都使用缩写。

诀窍是，我希望亚洲所有国家至少显示亚洲的缩写，即“ AS”，即使一个国家没有缩写（例如印度）。如果该国家/地区确实有缩写，
则结果应如下所示：China：CN，AS

我已经使用子查询部分地工作了，但是印度总是返回NULL作为缩写。它的行为就像没有返回到缩写的完整递归路径一样，然后它返回null。也许解决方案是在缩写表上使用左外部联接？我已经尝试了好几个小时，尝试了许多不同的变体，而且子查询也尽我所能。

WITH  abcd 
    AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID, 
                CAST(([Name]) AS VARCHAR(1000)) AS "Path" 
        FROM    @tbl 
        WHERE   ParentId IS NULL 
        UNION ALL 
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name + ':' +                 
                (
                    select t2.abbreviation + ','
                    from @tbl2
                    where t.id = t2.id
                )) AS VARCHAR(1000)) AS "Path"

        FROM    @tbl AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT * FROM abcd

顺便说一句，如果有问题，我正在使用sql server 2005

Answer 1

试试这个例子，它将为您提供输出（1个示例行）

id  Name    ParentID    Path    abbreviation    (No column name)
5   China   2   Asia/China  CN,AS   Asia/China:CN,AS

TSQL正在

DECLARE @tbl TABLE ( 
     Id INT 
    ,[Name] VARCHAR(20) 
    ,ParentId INT 
    )

INSERT INTO @tbl( Id, Name, ParentId ) 
VALUES 
 (1, 'Europe', NULL) 
,(2, 'Asia',   NULL) 
,(3, 'Germany', 1) 
,(4, 'UK',      1) 
,(5, 'China',   2) 
,(6, 'India',   2) 
,(7, 'Scotland', 4) 
,(8, 'Edinburgh', 7) 
,(9, 'Leith', 8)

; 
DECLARE @tbl2 table (id int, abbreviation varchar(10), tbl_id int)
INSERT INTO @tbl2( Id, Abbreviation, tbl_id ) 
VALUES 
 (100, 'EU', 1) 
,(101, 'AS', 2) 
,(102, 'DE', 3) 
,(103, 'CN', 5)

;WITH abbr AS (
    SELECT a.*, isnull(b.abbreviation,'') abbreviation
    FROM @tbl a
    left join @tbl2 b on a.Id = b.tbl_id
), abcd AS ( 
          -- anchor 
        SELECT  id, [Name], ParentID,
                CAST(([Name]) AS VARCHAR(1000)) [Path],
                cast(abbreviation as varchar(max)) abbreviation
        FROM    abbr
        WHERE   ParentId IS NULL 
        UNION ALL
          --recursive member 
        SELECT  t.id, t.[Name], t.ParentID, 
                CAST((a.path + '/' + t.Name) AS VARCHAR(1000)) [Path],
                isnull(nullif(t.abbreviation,'')+',', '') + a.abbreviation
        FROM    abbr AS t 
                JOIN abcd AS a 
                  ON t.ParentId = a.id 
       )
SELECT *, [Path] + ':' + abbreviation
FROM abcd