树状结构的阶层数的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如下:(阶层数从1开始)

SELECT Children.organizationid , COUNT(Parents.organizationid) AS level
  FROM organization_ Parents, organization_ Children
 WHERE Children.leftorganizationid BETWEEN Parents.leftorganizationid AND Parents.rightorganizationid
GROUP BY Children.organizationid;

 

结果例子:

13260,1
13290,1
10852,2
15300，3