如何在Oracle 11g中获取层次结构联接数据的根

debugcn 发表于 Dev

F0RR

我的架构如下所示。

我有分层区域：

create table district(
  id integer primary key,
  name varchar2(32),
  parent_id integer references district(id)
)

以及地区内的房屋：

create table house(
  id integer primary key,
  name varchar2(32),
  district_id integer references district(id)
)

house.district_id是始终在底部district的层次。如何选择每一个房子，并id和name该的根的district层次理论？

目前，我正在使用两个子查询，但是感觉不对：

select 
  h.id, 
  h.name,
  (
    select id from district
     where parent_id is null
     start with id = house.district_id
   connect by parent_id = id
  ) as district_id,
  (
    select name from district
     where parent_id is null
     start with id = house.district_id
   connect by parent_id = id
  ) as district_name
from house;

Oracle版本是11g第2版。

样本数据：地区

+-------------------+
| id name parent_id |
+-------------------+
| 1 'one' NULL      |
| 2 'two' 1         |
| 3 'three' 3       |
+-------------------+

房屋

id name district_id
1 'h1' 3
2 'h2' 3
3 'h3' 3

所需的输出：

+------------------------------------+
| id name district_id, district_name |
+------------------------------------+
| 1 'h1' 1 'one'                     |
| 2 'h2' 1 'one'                     |
| 3 'h3' 1 'one'                     |
+------------------------------------+

专线小巴

我喜欢为此使用递归with子句。您正在使用的Oracle起始版本11gR2中支持此功能。与connect by查询相比，我发现值得学习这种新语法，因为：

它基于标准的SQL规范（所有其他主要数据库在with子句中均支持递归）
特定于Oracle的方法更加灵活

考虑：

with cte (id, parent_id, root_id, root_name) as (
    select id, parent_id, id as root_id, name as root_name
    from district
    where parent_id is null
    union all
    select d.id, d.parent_id, c.root_id, c.root_name
    from   cte c
    inner join district d on d.parent_id = c.id
) search depth first by id set order1
select h.id, h.name, c.root_id, c.root_name
from house h
inner join cte c on c.id = h.district_id

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。