PostgreSQL 将父子表按照父子结构排序

最新推荐文章于 2024-07-07 07:41:00 发布

原创最新推荐文章于 2024-07-07 07:41:00 发布 · 926 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#PostgreSQL #SQL #父子结构 #树表 #父子顺序

PostgreSQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一种使用递归查询方法从数据库中获取层级结构数据，并结合特定字段进行组合排序的技术。通过示例展示了如何在SQL中实现这一过程，确保数据能够按路径和显示索引正确排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

示例:

WITH RECURSIVE T ( yhlxid, yhlxtitle, yhlxpid, PATH, DEPTH, showindex ) AS (
	SELECT
		yhlxid,
		yhlxtitle,
		yhlxpid,
		'' || yhlxid :: VARCHAR AS PATH,
		1 AS DEPTH,
		showindex 
	FROM
		gc_curing_yhlx 
	WHERE
		1 = 1 
		AND yhlxtype = 'curing' UNION ALL
	SELECT
		D.yhlxid,
		D.yhlxtitle,
		D.yhlxpid,
		T.PATH || '/' || D.yhlxid,
		T.DEPTH + 1 AS DEPTH,
		D.showindex 
	FROM
		gc_curing_yhlx D
		JOIN T ON D.yhlxpid = T.yhlxid 
	) SELECT
	yhlxid,
	yhlxtitle,
	yhlxpid,
	PATH,
	DEPTH,
	showindex 
FROM
T 
ORDER BY
	PATH,
	showindex;

进阶,组合排序:

WITH RECURSIVE T ( yhlxid, yhlxtitle, yhlxpid, PATH, DEPTH, showindex ) AS (
	SELECT
		yhlxid,
		yhlxtitle,
		yhlxpid,
		'' || yhlxid :: VARCHAR AS PATH,
		1 AS DEPTH,
		showindex 
	FROM
		gc_curing_yhlx 
	WHERE
		1 = 1 
		AND yhlxtype = 'curing' UNION ALL
	SELECT
		D.yhlxid,
		D.yhlxtitle,
		D.yhlxpid,
		T.PATH || '/' || D.showindex || D.yhlxid,
		T.DEPTH + 1 AS DEPTH,
		D.showindex 
	FROM
		gc_curing_yhlx D
		JOIN T ON D.yhlxpid = T.yhlxid 
	) SELECT
	yhlxid,
	yhlxtitle,
	yhlxpid,
	PATH,
	DEPTH,
	showindex 
FROM
T 
ORDER BY
	PATH,
	showindex;