假设我有以下表格：
表帐户：
parent_id | id
......
10 | 101
20 | 201
30 | 301
30 | 302
40 | 401
40 | 402
401 | 4011
401 | 4012
4012 | 40121
以及表帐户树：
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
我需要的是显示帐户ID的（父母）及其所有孩子，并为每个孩子显示他们的孩子，按深度升序分组；
到目前为止，我使用：
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;
它将返回按父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多个客户，他们的ID并不像我的例子中那样“可预测”编号，深度超过5层。
最佳答案
将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');
关于sql - PostgreSQL查询以按深度排序检索所有 child 的 parent ，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53054640/