SQL的递归查询应用场景:
在一个系统中往往需要保存机构,地区,菜单等等树状结构的数据,使用递归查询能够快速的获取这些树状结构数据的关联关系。
树形结构的表结构一般采用ID,PID进行主从节点的关联。
比如一个组织机构的表结构如下:
CREATE TABLE "SYS_ORG"
(
"ID" VARCHAR(32) NOT NULL,
"NAME" VARCHAR(100),
"PID" VARCHAR(32),
"SORT" INTEGER,
NOT CLUSTER PRIMARY KEY("ID")) ;
COMMENT ON TABLE "SYS_ORG" IS '部门';
COMMENT ON COLUMN "SYS_ORG"."ID" IS '部门ID';
COMMENT ON COLUMN "SYS_ORG"."NAME" IS '部门名称';
COMMENT ON COLUMN "SYS_ORG"."PID" IS '上级部门ID:0 为根部门';
COMMENT ON COLUMN "SYS_ORG"."SORT" IS '部门排序:部门排序号从1开始';
在达梦数据库中可以通过如下的SQL语句查询出组织机构的层级关系。
SELECT
ID ,
NAME ,
PID ,
SORT ,
LEVEL AS ORG_LEVEL,/*层级*/
SYS_CONNECT_BY_PATH(NAME, '') AS FULL_NAME,/*全名*/
CONNECT_BY_ROOT NAME AS ROOT_NAME,/*根节点名称*/
CONNECT_BY_ISLEAF AS LEAF /*是否叶子节点*/
FROM
SYS_ORG START
WITH PID = '0' CONNECT BY PRIOR(ID) = PID;
如以下测试数据:
insert into "SYS_ORG" ("ID","PID","NAME","SORT") values ('1', '0', '达梦数据库', 1);
insert into "SYS_ORG" ("ID","PID","NAME","SORT") values ('11', '1', '研发部', 1);
insert into "SYS_ORG" ("ID","PID","NAME","SORT") values ('111', '11', '研发一组', 1);
insert into "SYS_ORG" ("ID","PID","NAME","SORT") values ('112', '11', '研发二组', 2);
insert into "SYS_ORG" ("ID","PID","NAME","SORT") values ('12', '1', '销售部', 1);
查询结果如下: