时间戳

首页 > 解决方案 > 如何从数据库中获取人的全名

sql - 如何从数据库中获取人的全名

问题描述

我有这些桌子

具有以下数据的人员表

person_id    description

 1          first in the family
 2          second in the family
 3          third in the family
 4          fourth int the family
 5          fifth in the family

包含以下数据的person_name表

person_id  first_name

  1        Santiago
  2         Lautaro
  3          Lucas
  4          Franco
  5          Agustín

具有以下数据的父表

person_father_id  description
    1              father of Lautaro
    2              father of Lucas
    3              father of Franco
    4              father of Agustín

包含以下数据的子表

 person_child_id   person_father_id
      2                 1
      3                 2
      4                 3
      5                 4

如何在 select pl/sql 查询中获取 person_id 4 的人的全名（Agustín Franco Lucas Lautaro Santiago）。核心表是人

标签： sqloracle11ghierarchical-datarecursive-query

解决方案

您可以使用带有内联视图的分层查询，该内联视图首先将相关表连接在一起。内联视图的查询可能是：

select p.person_id, pn.first_name, c.person_father_id
from person p
join person_name pn on pn.person_id = p.person_id
left join children c on c.person_child_id = p.person_id;

 PERSON_ID FIRST_NAME PERSON_FATHER_ID
---------- ---------- ----------------
         2 Lautaro                   1
         3 Lucas                     2
         4 Franco                    3
         5 Agustín                   4
         1 Santiago

并以此作为分层查询的基础：

select trim(sys_connect_by_path(first_name, ' ')) as whole_name
from (
  select p.person_id, pn.first_name, c.person_father_id
  from person p
  join person_name pn on pn.person_id = p.person_id
  left join children c on c.person_child_id = p.person_id
)
where connect_by_isleaf = 1
start with person_id = 4
connect by person_id = prior person_father_id;

WHOLE_NAME                                        
--------------------------------------------------
Franco Lucas Lautaro Santiago

或者您可以使分层查询本身成为进一步的子查询，然后加入名称并聚合：

select listagg(pn.first_name, ' ') within group (order by lvl) as whole_name
from (
  select person_id, level as lvl
  from (
    select p.person_id, c.person_father_id
    from person p
    left join children c on c.person_child_id = p.person_id
  )
  start with person_id = 4
  connect by person_id = prior person_father_id
) t
join person_name pn on pn.person_id = t.person_id;

WHOLE_NAME                                        
--------------------------------------------------
Franco Lucas Lautaro Santiago

请注意，对于这两个表，您必须先加入表，然后才能根据您的起始 ID（start with而不是where，因为这是分层的）进行过滤。这意味着对于更大的表，它最终可能会完成比您真正需要或期望的更多的工作。

或者，如果您愿意，也可以使用递归子查询分解（递归 CTE）做同样的事情，并且您使用的是 Oracle 11gR2 或更高版本：

with r (person_id, person_father_id, lvl) as (
  select p.person_id, c.person_father_id, 1
  from person p
  left join children c on c.person_child_id = p.person_id
  where p.person_id = 4
  union all
  select p.person_id, c.person_father_id, r.lvl + 1
  from r
  join person p on p.person_id = r.person_father_id
  left join children c on c.person_child_id = p.person_id
)
select listagg(pn.first_name, ' ') within group (order by lvl) as whole_name
from r
join person_name pn on pn.person_id = r.person_id;

WHOLE_NAME                                        
--------------------------------------------------
Franco Lucas Lautaro Santiago

这看起来更复杂，但至少可以将过滤器放在递归 CTE 的锚成员中。

阅读有关分层查询和递归子查询分解的更多信息。

推荐阅读