首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在层次树中找到每个组的叶节点

问在层次树中找到每个组的叶节点
EN

Stack Overflow用户
提问于 2017-01-17 23:13:21
回答 1查看 459关注 0票数 0

我有以下表格。表T1中的类型列遵循表T2的层次结构。我想在表T1中列出每个名称的类型,并找到每个名称的叶。

代码语言:javascript
复制
      T1                              T2
name      type                    type       parent
----------------                --------------------
a1      person                |  artist     person       
a1      artist                |  actor      artist
a2      person                |  athlete    person
a2      athlete               |  person       ''
a3      person                       
a3      artist                
a3      actor                 

查找每个名称的叶节点的常规查询在这里不起作用。

代码语言:javascript
复制
select a.* from T1 a left join   T2 b on a.type = b.parent where b.parent is null; 
a2      athlete               
a3      actor   

但是我正在寻找这个输出(每组名称的叶节点)

代码语言:javascript
复制
a1      artist                
a2      athlete               
a3      actor

换句话说,我希望找到每个名称的叶节点,所以在a1的情况下,person与actor之间的叶节点将是actor,对于a3 person,actor,actor,它将是参与者。

是否有一种使用GROUP并获得所需输出的方法?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-01-17 23:27:26

这是代码

代码语言:javascript
复制
WITH cte AS (
SELECT
    t1.name AS anchor_name
    , t1.type AS type
    , t2.parent
    , 0 AS level
FROM #T1 t1 JOIN #T2 t2 ON t1.type = t2.type 

UNION ALL

SELECT cte.anchor_name, cte.type, t2.parent, cte.level+1
FROM cte JOIN #T2 t2 ON cte.parent = t2.type
)
, tmp AS (
    SELECT anchor_name, MAX(cte.level) AS max_level
    FROM cte 
    GROUP BY cte.anchor_name
)
SELECT cte.anchor_name as name, cte.type
FROM tmp JOIN cte ON cte.anchor_name = tmp.anchor_name AND cte.level = tmp.max_level

这是测试代码:

代码语言:javascript
复制
CREATE TABLE #T1 (
    name VARCHAR(2)
    , type VARCHAR(MAX)
    );

CREATE TABLE #T2 (
    type VARCHAR(MAX)
    , parent VARCHAR(MAX)
    );

INSERT INTO #T1 (name, type)
VALUES
('a1', 'artist')
,('a1', 'person')
,('a2', 'person')
,('a2', 'athlete')
,('a3', 'person')
,('a3', 'artist')
,('a3', 'actor');

INSERT INTO #T2 (type, parent)
VALUES
('artist'    , 'person')
,('actor'     , 'artist')
,('athlete'   , 'person')
,('person'    ,   '');


SELECT *
FROM #T1;

SELECT *
FROM #T2;

WITH cte AS (
    SELECT
        t1.name AS anchor_name
        , t1.type AS type
        , t2.parent
        , 0 AS level
    FROM #T1 t1 JOIN #T2 t2 ON t1.type = t2.type 

    UNION ALL

    SELECT cte.anchor_name, cte.type, t2.parent, cte.level+1
    FROM cte JOIN #T2 t2 ON cte.parent = t2.type
    )
, tmp AS (
    SELECT anchor_name, MAX(cte.level) AS max_level
    FROM cte 
    GROUP BY cte.anchor_name
)
SELECT cte.anchor_name as name, cte.type
FROM tmp JOIN cte ON cte.anchor_name = tmp.anchor_name AND cte.level = tmp.max_level

http://rextester.com/TRD23741

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/41708703

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档