Oracle: как я могу запросить иерархическую таблицу?

Фон

Это для построения некоторых представлений, которые мы будем использовать для отчетности.

У меня есть таблица местоположений, ключевые поля - это «местоположение» и «родительский» .

Структура, создаваемая этими двумя полями, по уровню, совпадает с названием компании -> Название Campus -> Название здания -> Название пола -> Название комнаты. Название компании остается тем же, и имя Campus остается таким же в этом случае.

Структура местоположений обычно выглядит так:

                                 +-----------+
                                 | Org. Name |
                                 +-----+-----+
                                       |
                                 +-----v-----+
           +--------------------+|Campus Name|+---+--+-------------+
           |                     +--+--------+    |                |
           |                        |             |                |
           |                        |             |                |
        +--+-----+           +------+-+        +--+----+       +---+---+
    +--+| BLDG-01|+--+       | BLDG-02|        |BLDG-03|       |Grounds|
    |   +--------+   |       +--------+        +-------+       +-------+
  +-+------+   +-----+--+
  |Floor-01|   |Basement+-------+
  +-+------+   +--------+       |
    |                           |
    |                           |
    | +----------+      +-------+--+
    +-+Room 1-001|      |Room B-002|
      +----------+      +----------+

Каждое место ссылается на родительское местоположение, которое в конечном счете является именем организации. В настоящее время существует только одна организация и один университетский городок.

Цели

  • Я хотел бы иметь возможность запрашивать все местоположения под любым заданным местоположением на уровне «Building». . Таким образом, я могу вернуть такие вещи, как количество рабочих операций для любого местоположения в данном здании.
  • Я хотел бы иметь возможность определить, какое подразделение принадлежит зданию . По сути, наоборот; Я хотел бы перейти с любого уровня ниже уровня здания и проследить до того, что это за здание.
  • Я хотел бы, чтобы это было в представлении . Это означает, что я хотел бы иметь таблицу, которая для каждого элемента на уровне «building» перечисляет здание в левом столбце и все возможные местоположения под этим зданием в правом столбце. Таким образом, у меня будет список, который я мог бы запросить в любое время, чтобы определить, какие местоположения являются частью здания.

Попытки и правильные действия

Я попытался сделать это через ужасно сконструированные представления, запросы UNION и т. д., которые все показались плохими. Я знаю, что Oracle обладает механизмом для этого через «CONNECT BY»; Я просто не знаю, как это использовать.

10 голосов | спросил SeanKilleen 28 J0000006Europe/Moscow 2012, 23:47:22

3 ответа


4

FrusteratedWithFormsDesigner имеет правильное направление (+1). Вот что я думаю, что вы ищете специально.

CREATE OR REPLACE VIEW BuildingSubs AS
   SELECT connect_by_root location "Building", location "SubLocation"
   FROM some_table l
   START WITH l.Location IN 
      (
         SELECT location FROM
         (
         SELECT level MyLevel, location FROM some_table 
         START WITH parent IS NULL 
         CONNECT BY PRIOR location=parent
         )
         WHERE MyLevel=3   
      )
   CONNECT BY PRIOR l.location = l.parent;

select * from BuildingSubs; 

Building             SubLocation        
-------------------- --------------------
BLDG-01              BLDG-01              
BLDG-01              Basement             
BLDG-01              Room B-002           
BLDG-01              Floor-01             
BLDG-01              Room 1-001           
BLDG-02              BLDG-02              
BLDG-03              BLDG-03              
Grounds              Grounds              

Представление выполняет все три цели. Вы можете запросить его для здания, чтобы найти все, что он содержит, и вы можете запросить его для подпозиции, чтобы найти, в каком здании он находится.

drop table some_table;
create table some_table (Location Varchar2(20), Parent Varchar2(20));

insert into some_table values ('Org. Name',NULL);
insert into some_table values ('MAINCAMPUS','Org. Name');
insert into some_table values ('BLDG-01','MAINCAMPUS');
insert into some_table values ('BLDG-02','MAINCAMPUS');
insert into some_table values ('BLDG-03','MAINCAMPUS');
insert into some_table values ('Grounds','MAINCAMPUS');
insert into some_table values ('Floor-01','BLDG-01');
insert into some_table values ('Basement','BLDG-01');
insert into some_table values ('Room B-002','Basement');
insert into some_table values ('Room 1-001','Floor-01');

Если вы не хотите считать здание само по себе одним из подполей, вы можете обернуть существующий запрос в один, то исключить записи, в которых здание и подзадача одинаковы.

ответил Leigh Riffel 3 J000000Tuesday12 2012, 21:51:34
9

CONNECT BY - это правильный способ обработки данных, которые являются естественно рекурсивными.

Я не знаю, как выглядит ваш стол, но может быть что-то вроде:

SELECT *
FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;

Это должно получить узлы под «BLDG-01».

Предложение START WITH - ваш базовый пример.

Еще одно объяснение (помимо Oracle, который, как я полагаю, вы уже читали и испытывали проблемы с ним, вероятно, очень кратким):

http://www.adp-gmbh.ch/ora/sql/connect_by.html

также:

http://psoug.org/reference/connectby.html

и

http://www.oradev.com/connect_by.jsp

ответил FrustratedWithFormsDesigner 28 J0000006Europe/Moscow 2012, 23:54:32
2

Я не уверен, что полностью понимаю ваш вопрос, но может быть что-то вроде этого:

select location, 
       parent,
       sys_connect_by_path(location, '/') as item_list,
       case level
         when 1 then 'building'
         when 2 then 'floor'
         when 3 then 'room'
       end as item_type
from some_table 
start with parent = 'MAINCAMPUS'
connect by prior location = parent;

Это покажет вам иерархию для каждого местоположения

ответил a_horse_with_no_name 3 J000000Tuesday12 2012, 20:12:47

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132