Запрос Postgresql для получения родителей со всеми дочерними элементами, упорядоченными по глубине

предположим, у меня есть следующие таблицы:

Таблица учетные записи:

parent_id | id

......
10         | 101
20         | 201
30         | 301
30         | 302
40         | 401
40         | 402
401        | 4011
401        | 4012
4012       | 40121

и таблица account_tree:

ancestor | descentant | depth

1         | 10         | 1
1         | 20         | 1
1         | 30         | 1
1         | 40         | 1
1         | 101        | 2
1         | 201        | 2
1         | 301        | 2
1         | 302        | 2
1         | 401        | 2
1         | 402        | 2
1         | 4011       | 3
1         | 4012       | 3
1         | 40121      | 4
10        | 101        | 1
20        | 201        | 1
30        | 301        | 1
30        | 302        | 1
40        | 401        | 1
40        | 402        | 1
40        | 4011       | 2
40        | 4012       | 2
40        | 40121      | 3
401       | 4011       | 1
401       | 4012       | 1
4012      | 40121      | 1

Мне нужно отобразить идентификаторы учетной записи (родителей) со всеми их дочерними элементами, и для каждого ребенка отобразить своих детей, сгруппированных по возрастанию глубины; До сих пор я использовал:

SELECT
    a.parent_id,
    a.id,
    p.depth
FROM
    accounts a
    INNER JOIN account_tree p ON a.id = p.descendant
    WHERE
        p.ancestor = 1
        AND p.depth <= 4
    ORDER BY
        a.parent_id;

который возвращает все учетные записи, упорядоченные по родительскому идентификатору. Мои ожидания:

parent_id | id        | depth

1         | 10         | 1
10        | 101        | 2
1         | 20         | 1
20        | 201        | 2
1         | 30         | 1
30        | 301        | 2
30        | 302        | 2
1         | 40         | 1
40        | 401        | 2
401       | 4011       | 3
401       | 4012       | 3
4012      | 40121      | 4
40        | 402        | 2

Я должен упомянуть, что в проекте, над которым я работаю, более 500 учетных записей, и их идентификаторы не имеют таких «предсказуемых» номеров, как в моем примере, а глубина превышает 5 уровней.

ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
0
308
2

Ответы 2

Это случай рекурсивного CTE. Я предполагаю, что ваши ключи на самом деле являются varchar, поскольку они не представляют числовые значения - если нет, то обновите в соответствии с моей последней частью.

Это создаст тестовые данные. вам нужна только ваша таблица Account, а не ваша древовидная таблица

create table #account(parent_id varchar(10), id varchar(10))
insert #account values ('1','10'),('1','20'),('1','30'),('1','40'),('10','101'),('20','201'),('30','301')
    ,('30','302'),('40','401'),('40','402'),('401','4011'),('401','4012'),('4012','40121')

Теперь ваш рекурсивный запрос: сначала найдите все строки верхнего уровня (parent = 1), затем найдите всех дочерних элементов, создавая составной путь по мере продвижения.

;with tree as (
    select parent_id, id, convert(varchar(100),id) as idPath, 1 as depth 
    from #account 
    where parent_id=1
    union all 
    select a.parent_id, a.id, convert(varchar(100),idPath+a.id) as idPath, depth+1 as depth
    from #account a
    join tree on tree.id=a.parent_id
)
select parent_id, id, depth from tree order by idpath

Если идентификаторы на самом деле являются целочисленными типами данных, измените конкатенацию на

convert(varchar(100),convert(varchar(10),idPath)+convert(varchar(10),a.id)) as idPath

Все данные большие, глубина целочисленная. Мои учетные записи выглядят так (пример): parent_id 807 имеет дочерние элементы, например 23456, 39887,12345

Stan Andrei 30.10.2018 09:41

Адаптация ответа TomC к postgres

with recursive tree as (
    select parent_id, id, lpad(id::varchar(12),12,'0')::varchar(144) as idPath, 1::int as depth 
    from accounts 
    where parent_id = '1'
    union all 
    select a.parent_id, a.id, concat(idPath, lpad(a.id,12,'0'))::varchar(144)  idPath, depth + 1::int as depth
    from accounts a
    join tree on tree.id=a.parent_id
)
select parent_id, id, depth, idpath
from tree 
order by idpath

Если вы используете lpad () разную длину строки учетной записи на любом уровне, это не влияет на общий порядок. Вам необходимо выбрать длину, соответствующую вашим фактическим номерам счетов. Я использовал 12, и объединенный путь должен быть кратным любому выбранному вами числу.

+----+-----------+-------+-------+--------------------------------------------------+
|    | parent_id | id    | depth | idpath                                           |
+----+-----------+-------+-------+--------------------------------------------------+
| 1  | 1         | 10    | 1     | 000000000010                                     |
| 2  | 10        | 101   | 2     | 000000000010000000000101                         |
| 3  | 1         | 20    | 1     | 000000000020                                     |
| 4  | 20        | 201   | 2     | 000000000020000000000201                         |
| 5  | 1         | 30    | 1     | 000000000030                                     |
| 6  | 30        | 301   | 2     | 000000000030000000000301                         |
| 7  | 30        | 302   | 2     | 000000000030000000000302                         |
| 8  | 1         | 40    | 1     | 000000000040                                     |
| 9  | 40        | 401   | 2     | 000000000040000000000401                         |
| 10 | 401       | 4011  | 3     | 000000000040000000000401000000004011             |
| 11 | 401       | 4012  | 3     | 000000000040000000000401000000004012             |
| 12 | 4012      | 40121 | 4     | 000000000040000000000401000000004012000000040121 |
| 13 | 40        | 402   | 2     | 000000000040000000000402                         |
+----+-----------+-------+-------+--------------------------------------------------+

использованные образцы данных:

CREATE TABLE accounts(
   parent_id VARCHAR(12) 
  ,id        VARCHAR(12) 
);
INSERT INTO accounts(parent_id,id) VALUES ('1','10');
INSERT INTO accounts(parent_id,id) VALUES ('1','20');
INSERT INTO accounts(parent_id,id) VALUES ('1','30');
INSERT INTO accounts(parent_id,id) VALUES ('1','40');
INSERT INTO accounts(parent_id,id) VALUES ('10','101');
INSERT INTO accounts(parent_id,id) VALUES ('20','201');
INSERT INTO accounts(parent_id,id) VALUES ('30','301');
INSERT INTO accounts(parent_id,id) VALUES ('30','302');
INSERT INTO accounts(parent_id,id) VALUES ('40','401');
INSERT INTO accounts(parent_id,id) VALUES ('40','402');
INSERT INTO accounts(parent_id,id) VALUES ('401','4011');
INSERT INTO accounts(parent_id,id) VALUES ('401','4012');
INSERT INTO accounts(parent_id,id) VALUES ('4012','40121');

Вы обнаружили интересную проблему в вопросе OP - образцы данных будут работать независимо от того, заполнены ли они целыми числами или нет - интересно посмотреть, нужны ли они или нет. Наверное, есть. Спасибо за версию Postgres.

TomC 31.10.2018 05:30

Другие вопросы по теме