帮助递归 CTE 查询加入第二个表

问题描述

我的目标是通过表 tbl 进行递归，并在通过该表进行递归时从另一个表 tbl2 中选择一个国家缩写(如果存在)并将这些结果附加在一起，这些结果将包含在最终输出中.

My objective is to recurse through table tbl and while recursing through that table select a country abbreviation (if it exists) from another table tbl2 and append those results together which are included in the final output.

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

The example I'll use will come from this post

tbl2 有一个指向 tbl 的外键 'tbl_id'，看起来像这样

tbl2 has a Foreign Key 'tbl_id' to tbl and looks like this

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

*注意:并非所有国家都有缩写.

*Note: not all the countries have abbreviations.

诀窍是，我希望亚洲的所有国家/地区至少显示亚洲的缩写，即AS"，即使一个国家/地区没有缩写(例如印度).如果国家/地区确实有缩写结果需要如下所示:China:CN,AS

The trick is, I want all the countries in Asia to at least show the abbreviation of Asia which is 'AS' even if a country doesn't have an abbreviation (like India for example). If the country does have an abbreviation the result needs to look like this: China:CN,AS

我已经使用子查询部分地工作了，但是 India 总是返回 NULL 作为缩写.这就像如果没有返回缩写的完整递归路径，那么它返回 null.也许解决方案是在缩写表上使用左外连接?我已经尝试了几个小时许多不同的变体，并且子查询尽可能接近.

I've got it partly working using a subquery, but India always returns NULL for the abbreviation. It's acting like if there isn't a full recursive path back to the abbreviation, then it returns null. Maybe the solution is to use a left outer join on the abbreviation table? I've tried for hours many different variations and the subquery is as close as I can get.

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

btw, I'm using sql server 2005 if that matters

推荐答案

试试这个例子，它会给你输出(1个样本行)

Try this example, which will give you the output (1 sample row)

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

这篇关于帮助递归 CTE 查询加入第二个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！