предположим, у меня есть следующие таблицы:
Таблица учетные записи:
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 уровней.
Это случай рекурсивного 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
Адаптация ответа 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.
Все данные большие, глубина целочисленная. Мои учетные записи выглядят так (пример): parent_id 807 имеет дочерние элементы, например 23456, 39887,12345