在保持结构的同时对树进行排序
|
我有一棵使用物化路径的树。
该表类似于：
+-------------+--------+----------+-------+
| path | depth | children | score |
+-------------+--------+----------+-------+
| 0001 | 1 | 3 | 5 |
| 00010001 | 2 | 0 | -3 |
| 00010002 | 2 | 0 | 27 |
| 00010003 | 2 | 0 | 10 |
| 0002 | 1 | 2 | 12 |
| 00020001 | 2 | 0 | 0 |
| 00020002 | 2 | 1 | 3 |
| 00020002001 | 3 | 0 | 1 |
+-------------+--------+----------+-------+
score
+-------------+--------+----------+-------+
| path | depth | children | score |
+-------------+--------+----------+-------+
| 0002 | 1 | 2 | 12 |
| 00020002 | 2 | 1 | 3 |
| 00020002001 | 3 | 0 | 1 |
| 00020001 | 2 | 0 | 0 |
| 0001 | 1 | 3 | 5 |
| 00010002 | 2 | 0 | 27 |
| 00010003 | 2 | 0 | 10 |
| 00010001 | 2 | 0 | -3 |
+-------------+--------+----------+-------+
path
SELECT path, depth, children, score FROM mytable ORDER BY path ASC
没有找到相关结果
已邀请:
1 个回复
仿普
with recursive ordered_tree as ( select tree.*, array[row_number() over w] as score_path from tree where depth = 1 window w as (order by tree.score desc) union all select tree.*, parent.score_path || array[row_number() over w] as score_path from tree join ordered_tree as parent on parent.id = tree.parent_id window w as (partition by tree.parent_id order by tree.score desc) ) select * from ordered_tree order by score_path