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

Question

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

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

tbl2 有一个到 tbl 的外键“tbl_id”，看起来像这样

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