Блог

У меня есть структура таблицы:

Регион:

• идентификатор (ключ)
• parent_region (внешний ключ — ссылается на другой регион)
• Имя

Предположим, у меня есть следующие 3 строки:

 [ id, parent_region, name ]
(0, NULL, "USA")
(1, 0, "Georgia")
(2, 1, "Atlanta")
  

Я хочу иметь возможность выбирать Атланту, Джорджия, США, запрашивая идентификатор:

 SELECT ... 
  FROM region 
 WHERE id = 2
  

… и возвращает 3 строки. Я использую Java / JDBC, поэтому, очевидно, я мог бы программно выбрать 3 строки одну за другой… но мне любопытно, есть ли умный SQL-способ сделать это.

Я использую MySQL, но мне также было бы любопытно узнать о решении Oracle или Informix.

В MySQL нет рекурсивных выборок, что слишком плохо для этой проблемы.

WITH RECURSIVE Предложение было придумано для этого. Это работает в PostgreSQL и что-то очень близкое или идентичное в Oracle.

 WITH RECURSIVE T(id, parent_region, name) AS
 SELECT id, parent_region, name FROM region WHERE id=2
 UNION
 SELECT id, parent_region, name FROM region JOIN T
   ON (region.parent_region = T.id) IS TRUE -- IS TRUE forces T.id NOT NULL
SELECT id, parent_region, name FROM T;
  

(Это будет повторяться бесконечно, если у вас есть циклы. Смотрите здесь объяснение того, как этого избежать.)

Этот запрос:

 select a.name, b.name, c.name 
from region a, region b, region c  
where a.parent_region = b.id and b.parent_region = c.id and a.id = 2;
  

Выдал эти результаты

  --------- --------- ------ 
| name    | name    | name |
 --------- --------- ------ 
| Atlanta | Georgia | USA  |
 --------- --------- ------ 
1 row in set (0.00 sec)