sql - 具有名称和最接近的富裕祖先的行

Question

您不仅要返回所有的人和祖先，而且要返回所有只有最接近的祖先的人，该如何返回？

我之前写过一堆递归查询，但这使我感到困惑和着迷。该图显示了层次结构。

我保证这不是家庭作业的问题:)。



这是测试数据：

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)

Answer 1

诀窍是，在您的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达到最佳性能。