SQL树查询-带有递归和联合。如何按第二个值排序
SQL树查询-带有递归和联合。如何按第二个值排序
提问于 2020-11-13 16:15:06
我有一个具有树状结构的类别表(见下图)。对于一个快速查询，我有以下SQL来获得完整的树。
WITH RECURSIVE search_tree(id, name, path, position) AS
(
SELECT id, name, ARRAY[id], position
FROM table_name
WHERE id = id
UNION ALL
SELECT table_name.id, table_name.name, path || table_name.id,
table_name.position
FROM search_tree
JOIN table_name ON table_name.parent_id = search_tree.id
WHERE NOT table_name.id = ANY(path)
)
SELECT id, name, path, position
FROM search_tree
ORDER BY path此查询结果在下表中。
id | name | path | position
----+--------------+---------+-------------
1 | Cat Pictures | {1}. |. 0
2 | Funny | {1,2}. |. 0
3 | LOLCats | {1,2,3} |. 1
4 | Animated | {1,2,4} |. 2
5 | Classic | {1,2,5} |. 0
6 | Renaissance | {1,6} |. 1因此，按路径排序是很好的。但是，如果路径级别是相同的级别(比如id 2& 4，和3，4，5)，那么我需要的是根据列位置的顺序。
因此，ID希望的顺序是
ids: 1, 6, 2, 5, 3, 4
如何更改SQL语句以反映该顺序？
回答 1
Stack Overflow用户
回答已采纳
发布于 2020-11-13 16:30:15
可以通过这种方式实现https://www.db-fiddle.com/f/rpFiPjKSwHW88C4cp6o9Rm/0
with recursive search_tree(id, parentPath, name) as (
select id, cast(row_number() over(order by pos) as text), name
from objects
where parent_id = 0
union all
select o.id, concat(search_tree.parentPath, ',', cast(row_number() over(order by o.pos) as text)), o.name
from search_tree
join objects as o on search_tree.id = o.parent_id
)
select *
from search_tree
order by parentPath;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64824310
复制相关文章
相似问题
腾讯云开发者
