您不仅要返回所有的人和祖先,而且要返回所有只有最接近的祖先的人,该如何返回?
我之前写过一堆递归查询,但这使我感到困惑和着迷。该图显示了层次结构。
我保证这不是家庭作业的问题:)。
这是测试数据:
create table test_people (
id_person int unique not null
,ancestor_id_person int
,name varchar(10) unique not null
,wealth varchar(4) not null
);
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(1, null, 'Abby', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(2, 1, 'Barry', 'rich');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(3, 1, 'Dan', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(4, 1, 'Elaine', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(5, 1, 'Frank', 'rich');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(6, 1, 'Gary', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(7, 2, 'Hank', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(8, 3, 'Irene', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(9, 4, 'Jack', 'rich');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(10, 5, 'Kelly', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(11, 5, 'Nancy', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(12, 6, 'Larry', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(13, 6, 'Mike', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(14, 7, 'Pearl', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(15, 7, 'Rick', 'poor');
insert into test_people (id_person, ancestor_id_person, name, wealth) values
(16, 9, 'Steve', 'rich');
这是我想要的输出:
NAME, NEAREST_WEALTHY_ANCESTOR
Abby, null
Barry, Barry
Dan, null
Elane, null
Frank, Frank
Gary, null
Hank, Barry
Irene, null
Jack, Jack
Kelly, Frank
Nancy, Frank
Larry, null
Mike, null
Pearl, Barry
Rick, Barry
Steve, Steve
这是我最新的尝试(完全错误):
with ancestors as (
select 1 as level, id_person, ancestor_id_person, name, wealth
from test_people
where id_person = 1
UNION ALL
select parent.level + 1, child.id_person, child.ancestor_id_person, child.name, child.wealth
from ancestors as parent,
test_people as child
where parent.id_person = child.ancestor_id_person
)
select T.id_person, A.ancestor_id_person, A.name, T.name, T.ancestor_id_person
from test_people as T
left outer join ancestors as A on T.ancestor_id_person = max(A.ancestor_id_person, A.id_person)
最佳答案
诀窍是,在您的CTE中,要为富裕祖先的名称添加一列,并针对要处理的每一行,如果该人有钱,则将其设置为当前名称,否则将现有值不加更改地传递(对于如果这些人很穷,则显示根行):
WITH RECURSIVE ancestors AS
(SELECT id_person, name
, CASE wealth WHEN 'rich' THEN name ELSE NULL END AS nearest_wealthy_ancestor
FROM test_people WHERE ancestor_id_person IS NULL
UNION ALL
SELECT t.id_person, t.name
, CASE t.wealth WHEN 'rich' THEN t.name ELSE a.nearest_wealthy_ancestor END
FROM test_people AS t JOIN ancestors AS a ON t.ancestor_id_person = a.id_person)
SELECT name, nearest_wealthy_ancestor FROM ancestors ORDER BY name;
name nearest_wealthy_ancestor
---------- ------------------------
Abby null
Barry Barry
Dan null
Elaine null
Frank Frank
Gary null
Hank Barry
Irene null
Jack Jack
Kelly Frank
Larry null
Mike null
Nancy Frank
Pearl Barry
Rick Barry
Steve Steve
注意:此查询将受益于
test_people(ancestor_id_person)
上的索引,并且您的id_person
列应为INTEGER PRIMARY KEY
,以使sqlite达到最佳性能。关于sql - 具有名称和最接近的富裕祖先的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/57209433/