Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > выбор населенного пункта со всеми предками

Автор: gulitskiy 25.9.2012, 14:18

Есть база, в ней хранятся область, район, населенный пункт.

Код


CREATE TABLE IF NOT EXISTS `places` (
    `id` SERIAL,
    `pid` BIGINT UNSIGNED DEFAULT NULL,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`pid`)
    REFERENCES `places`(`id`)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Код


INSERT INTO `places` (`pid`, `name`, `zip`, `country`) VALUES
    (NULL, 'Первая область', NULL, 'KZ'),
        (1, 'Первый район', NULL, 'KZ'),
        (1, 'Второй район', NULL, 'KZ'),
        (1, 'Третий район', NULL, 'KZ'),
            (4, 'Поселок один', '020801', 'KZ'),
            (4, 'Поселок 2', '020800', 'KZ'),
        (1, 'четвертый район район', NULL, 'KZ'),
        (1, 'город 1', NULL, 'KZ');

как составить запрос чтобы в итоге выводилось все нас пункты в таком виде

область район поселок
область город нулл

и тд

Автор: Akina 25.9.2012, 14:26

Курите маны по "nested set"

Автор: gulitskiy 25.9.2012, 14:35

Цитата(Akina @ 25.9.2012, 14:26)

Курите маны по "nested set"

спасибо про нестед сет я знаю

возможно ли это с ним?
допустим есть город Станция и деревня Станция
у города есть только один предок область а у деревни 2 район и область

я делаю запрос селект where name like "Стан%"

и мне надо что он выдал 3 столбика

область район поселок станция
область город станция

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

Автор: tzirechnoy 25.9.2012, 18:54

Я думаю, через LEFT OUTER JOIN лучшэ всего обойтись.

Автор: gulitskiy 26.9.2012, 07:33

Цитата(tzirechnoy @ 25.9.2012, 18:54)

Я думаю, через LEFT OUTER JOIN лучшэ всего обойтись.

за оутер спасибо

теперь вот что получается

строю запрос

Код


select CONCAT(
    IF(p1.name IS NOT NULL, p1.name, ''),
    IF(p2.name IS NOT NULL, CONCAT(', ', p2.name), ''),
    IF(p3.name IS NOT NULL, CONCAT(', ', p3.name), '')) as place
    from `places` as p1
        left outer join `places` as p2 on p1.id = p2.pid
        left outer join `places` as p3 on p2.id = p3.pid
        where p3.name like 'Аста%' or p2.name like 'Аста%';

он выдает

Акмолинская область, Астана

строю другой запрос

Код


select CONCAT(
    IF(p1.name IS NOT NULL, p1.name, ''),
    IF(p2.name IS NOT NULL, CONCAT(', ', p2.name), ''),
    IF(p3.name IS NOT NULL, CONCAT(', ', p3.name), '')) as place
    from `places` as p1
        left outer join `places` as p2 on p1.id = p2.pid
        left outer join `places` as p3 on p2.id = p3.pid
        where p3.name like 'Ново%' or p2.name like 'Ново%';

и он выдает

Акмолинская область, Ерейментауский район, Новодолинка
Акмолинская область, Ерейментауский район, Новомарковка
Ерейментауский район, Новодолинка
Ерейментауский район, Новомарковка

то есть данные дублируются

Автор: Akina 26.9.2012, 07:42

Цитата(gulitskiy @ 26.9.2012, 08:33

)

за оутер спасибо

Судя по синтаксису, это MySQL. В нём LEFT JOIN и LEFT OUTER JOIN - полные синонимы.

Цитата(gulitskiy @ 26.9.2012, 08:33

)

теперь вот что получается

У Вас структура кривая - нет возможности определить, является ли запись областью, районом, нас. пунктом или иным объектом.
Введите, что ли, проверку, что элемент в копии таблицы p1 является в свойм дереве корневым (потребуется четвёртая копия таблицы в запросе).

Автор: gulitskiy 26.9.2012, 07:56

Цитата(Akina @ 26.9.2012, 07:42

)

У Вас структура кривая

понятно, а как можно сделать не кривую структупу

как понимаю, енум типа область, район, нас пункт?

Автор: Akina 26.9.2012, 08:15

Как вариант...