我有以下表格。表T1中的类型列遵循表T2的层次结构。我想在表T1中列出每个名称的类型,并找到每个名称的叶。
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 查找每个名称的叶节点的常规查询在这里不起作用。
select a.* from T1 a left join T2 b on a.type = b.parent where b.parent is null;
a2 athlete
a3 actor 但是我正在寻找这个输出(每组名称的叶节点)
a1 artist
a2 athlete
a3 actor换句话说,我希望找到每个名称的叶节点,所以在a1的情况下,person与actor之间的叶节点将是actor,对于a3 person,actor,actor,它将是参与者。
是否有一种使用GROUP并获得所需输出的方法?
发布于 2017-01-17 23:27:26
这是代码
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这是测试代码:
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_levelhttps://stackoverflow.com/questions/41708703
复制相似问题