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

前提有一张组织表.

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);