时间戳

首页 > 解决方案 > 有名字和最近富有的祖先的行

sql - 有名字和最近富有的祖先的行

问题描述

你如何不仅返回所有的人和祖先，而且返回所有只有他们最近的祖先碰巧被装载的人？

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

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

在此处输入图像描述

这是测试数据：

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)

标签： sqlsqliterecursion

解决方案

诀窍是，在您的 CTE 中，为富有的祖先的名称包含一列，并且对于正在处理的每一行，如果该人富有，则将其设置为当前名称，否则将现有值不变地传递（或 null如果这些人很穷，根行）：

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 获得最佳性能的列。

推荐阅读