За последние 24 часа нас посетил 20831 программист и 1141 робот. Сейчас ищут 674 программиста ...

Хитровыдуманный запрос для таблицы с иерархией

Тема в разделе "MySQL", создана пользователем polin11, 12 апр 2019.

Метки:
  1. polin11

    polin11 Активный пользователь

    С нами с:
    22 янв 2019
    Сообщения:
    20
    Симпатии:
    0
    Есть иерархическая таблица с каталогами данных (в примере корнями каталогов являются Корень1, Корень2 у них поле parent и root
    заполнено null)
    Рабочий пример
    https://www.db-fiddle.com/f/2hbRo3qUfhwACNHVBxy3A9/0

    CREATE TABLE rec (
    id INTEGER,
    full_code VARCHAR (255),
    code VARCHAR (255),
    parent INTEGER,
    root INTEGER,
    is_node bool
    ) ;--DEFAULT CHARSET=utf8 ;

    INSERT INTO rec
    (id, full_code, code, parent, root, is_node)
    VALUES
    (1, 'Корень1','Корень1', null, null, true),
    (2, 'Корень1.2017-12-31', '2017-12-31', 1, 1, false),
    (3, 'Корень1.2016-12-31', '2016-12-31', 1, 1, false),
    (4, 'Корень1.2015-12-31', '2015-12-31', 1, 1, false),
    (5, 'Корень1.111', '111', 1, 1, null),
    (6, 'Корень1.111', '111', 4, 4, null),
    (7, 'Корень1.111', '111', 3, 3, null),
    (8, 'Корень1.111', '111', 2, 2, null),
    (9, 'Корень1.222', '222', 2, 2, null),
    (10, 'Корень1.333', '333', 3, 3, null),
    (11, 'Корень2','Корень2', null, null, true)
    В каждом каталоге есть папки с датой, в этой папке лежат записи актуальные до указанной даты,
    у таких папок поле is_node имеет значение false.

    Нужно написать запрос, который по значению full_code и дате, вернет запись актуальную на указанную дату.
    Если будет передан полный код корня каталога или полный код записи с папкой, то нужно вернуть
    актуальную папку с датой.
    Например:
    1) если full_code = 'Корень1.111' Дата = '2017-02-02', то должно вернуться
    (8, 'Корень1.111', '111', 2, 2, null)
    2) если full_code = 'Корень1.111' Дата = '2019-02-02', то должно вернуться из корня запись
    (5, 'Корень1.111', '111', 1, 1, null)
    3) если full_code = 'Корень1.111' Дата = '2012-02-02', то должно ничего не вернут, так как
    есть папка с датой 2015-12-31 в ней нет записи с кодом 'Корень1.111'
    4) если full_code = 'Корень1' Дата = '2017-02-02', то должно вернуться
    (2, 'Корень1.2017-12-31', '2017-12-31', 1, 1, false)
    5) если full_code = 'Корень1' Дата = '2019-02-02', то должно вернуться
    (1, 'Корень1','Корень1', null, null, true)
    6) если full_code = 'Корень1.2017-12-31' Дата = '2019-02-02', то должно вернуться
    (1, 'Корень1','Корень1', null, null, true)
    Написал запрос, логика такая узнаем значение id корня каталога, затем строим
    список из корня и папок с датами, сравниваем с переданной датой, берем актуальную запись с датой,
    проверяем есть ли там искомый полный код.
    Но запрос получился слишком сложный:
    WITH ROOT AS(
    select (
    case WHEN r1.root is NULL THEN r1.id
    WHEN r1.is_node is False THEN r1.root
    else (select case WHEN r2.root is NULL THEN r2.id else r2.root end from catalog r2 where r2.id = r1.root LIMIT 1) end ) as root
    from catalog r1
    where r1.full_code = 'Корень1.111'
    limit 1),
    T AS(
    select id
    from catalog
    where (parent = (select * from root) and is_node is false
    AND code >= '2017-02-02'::text) OR id= (select * from root) ORDER BY parent NULLS LAST, code
    LIMIT 1 ),
    IDS AS(
    select ( select * from T ) as id
    from catalog
    where (parent is null or is_node is false) and full_code = 'Корень1.111'
    union
    select id
    from catalog
    where root = (select * from t) and full_code = 'Корень1.111' and (parent is not null and is_node is not false)
    limit 1)
    SELECT *
    FROM catalog
    where id in (select * from ids)

    Вопросы такие:
    1) Как упростить запрос?
    2) Главный вопрос, как написать запрос, если передан массив полных кодов, и нужно получить массив актуальных записей.
    Даже нет идей, как решить этот вопрос???
     
  2. Babka_Gadalka

    Babka_Gadalka Активный пользователь

    С нами с:
    16 фев 2019
    Сообщения:
    162
    Симпатии:
    23
    Адрес:
    Москва, Пушкина, Избушкина, 2й этаж душечка.
    я вижу взаимосвязь
    с чего брать и у кого сверять через
    --- Добавлено ---
    но не вижу в примерах данных