树状结构的罗列所有的节点的sql

最新推荐文章于 2024-10-14 09:03:04 发布

步行者

最新推荐文章于 2024-10-14 09:03:04 发布

阅读量818

点赞数

分类专栏：数据库文章标签： character integer comments sql table null

本文链接：https://blog.youkuaiyun.com/hantiannan/article/details/6069822

版权

数据库专栏收录该内容

62 篇文章

订阅专栏

本文介绍了一种基于左值和右值的树形结构组织表的SQL查询方法，通过连接多个表来展示组织结构，并按阶层罗列出所有节点。

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

前提有一张组织表.

CREATE TABLE organization_
(
organizationid bigint NOT NULL,
companyid bigint,
parentorganizationid bigint,
leftorganizationid bigint,
rightorganizationid bigint,
"name" character varying(100),
type_ character varying(75),
recursable boolean,
regionid bigint,
countryid bigint,
statusid integer,
comments text,
CONSTRAINT organization__pkey PRIMARY KEY (organizationid)
)

树节点的按照阶层的把所有的节点罗列出来的sql如下:

SELECT Mgrs.organizationid
FROM organization_ Mgrs,
      organization_ MidMgrs,
      organization_ Workers
WHERE Mgrs.leftorganizationid BETWEEN MidMgrs.leftorganizationid AND MidMgrs.rightorganizationid
   AND MidMgrs.leftorganizationid BETWEEN Workers.leftorganizationid AND Workers.rightorganizationid
GROUP BY Mgrs.organizationid, Mgrs.leftorganizationid
ORDER BY MAX(Mgrs.leftorganizationid);