我在项目研发过程中，到设计表的这一环节，经常会遇到树结构的设计，这个查询让我非常头疼，以前的处理都是写手递归函数，这种方式虽然可以解决，但是很不方便。今天我们依赖数据库处理它。

1、MySQL

1.1、建表并插入数据

CREATE TABLE IF NOT EXISTS `DS_CATALOG` (
  `CATALOG_ID` int(3) NOT NULL COMMENT 'ID',
  `CATALOG_PARENT_ID` int(3) DEFAULT NULL COMMENT '父ID',
  `CATALOG_NAME` varchar(255) DEFAULT NULL COMMENT '目录名称',
  `CATALOG_OWNER` varchar(255) DEFAULT NULL COMMENT '目录所有者',
  `ORDER_INDEX` int(3) DEFAULT NULL COMMENT '排序索引',
  PRIMARY KEY (`CATALOG_ID`)
);
INSERT INTO DS_CATALOG VALUES (1,0,'admin','10001',1);
INSERT INTO DS_CATALOG VALUES (2, 1, 'test2', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (3, 1, 'test3', '10001', 2);      
INSERT INTO DS_CATALOG VALUES (4, 1, 'test4', '10001', 3);      
INSERT INTO DS_CATALOG VALUES (5, 1, 'test5', '10001', 4);      
INSERT INTO DS_CATALOG VALUES (6, 1, 'test6', '10001', 5);      
INSERT INTO DS_CATALOG VALUES (7, 1, 'test7', '10001', 6);      
INSERT INTO DS_CATALOG VALUES (8, 2, 'test8', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (9, 3, 'test9', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (10, 2, 'test10', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (11, 3, 'test11', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (12, 6, 'test12', '10001', 1);    
INSERT INTO DS_CATALOG VALUES (13, 7, 'test13', '10001', 1);    

1.2、创建自定义函数

DROP FUNCTION IF EXISTS DS_GET_CHILD_CATALOG;|
CREATE FUNCTION DS_GET_CHILD_CATALOG(rootID varchar(100))
	RETURNS varchar(2000)
BEGIN
	DECLARE arr varchar(2000);
	DECLARE pid varchar(100);
	SET arr = '$';
	SET pid = rootID;
	WHILE pid is not null DO
    	SET arr = concat(arr, ',', pid);
    	SELECT group_concat(CATALOG_ID) INTO pid FROM DS_CATALOG where FIND_IN_SET(CATALOG_PARENT_ID, pid);
	END WHILE;
	RETURN arr;
END;

1.3、查询

SELECT * FROM DS_CATALOG FIND_IN_SET(CATALOG_ID,DS_GET_CHILD_CATALOG(1))

2、Oracle与达梦（Oracle与达梦一致）

1.1、创建表并插入语句

CREATE TABLE "DS_CATALOG" (
	"CATALOG_ID" NUMBER(3) NOT NULL ,
	"CATALOG_PARENT_ID" NUMBER(3) NULL ,
	"CATALOG_NAME" VARCHAR2(255) NULL ,
	"CATALOG_OWNER" VARCHAR2(255) NULL ,
	"ORDER_INDEX" NUMBER(3) NULL ,
	PRIMARY KEY ("CATALOG_ID")
);
COMMENT ON COLUMN "DS_CATALOG"."CATALOG_ID" IS 'ID';
COMMENT ON COLUMN "DS_CATALOG"."CATALOG_PARENT_ID" IS '父ID';
COMMENT ON COLUMN "DS_CATALOG"."CATALOG_NAME" IS '目录名称';
COMMENT ON COLUMN "DS_CATALOG"."CATALOG_OWNER" IS '目录所有者';
COMMENT ON COLUMN "DS_CATALOG"."ORDER_INDEX" IS '排序索引';

INSERT INTO DS_CATALOG VALUES (1,0,'admin','10001',1);|
INSERT INTO DS_CATALOG VALUES (2, 1, 'test2', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (3, 1, 'test3', '10001', 2);      
INSERT INTO DS_CATALOG VALUES (4, 1, 'test4', '10001', 3);      
INSERT INTO DS_CATALOG VALUES (5, 1, 'test5', '10001', 4);      
INSERT INTO DS_CATALOG VALUES (6, 1, 'test6', '10001', 5);      
INSERT INTO DS_CATALOG VALUES (7, 1, 'test7', '10001', 6);      
INSERT INTO DS_CATALOG VALUES (8, 2, 'test8', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (9, 3, 'test9', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (10, 2, 'test10', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (11, 3, 'test11', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (12, 6, 'test12', '10001', 1);    
INSERT INTO DS_CATALOG VALUES (13, 7, 'test13', '10001', 1);    

1.2、查询

SELECT * FROM DS_CATALOG START WITH CATALOG_ID = 1 CONNECT BY PRIOR CATALOG_ID=CATALOG_PARENT_ID

3、PostgreSQL

3.1、创建表并插入语句

create table ds_catalog (
	catalog_id int primary key not null ,
	catalog_parent_id int null ,
	catalog_name varchar(255) null ,
	catalog_owner varchar(255) null ,
	order_index int null
);
comment on column ds_catalog.catalog_id is 'id';
comment on column ds_catalog.catalog_parent_id is '父id';
comment on column ds_catalog.catalog_name is '目录名称';
comment on column ds_catalog.catalog_owner is '目录所有者';
comment on column ds_catalog.order_index is '排序索引';

insert into ds_catalog values (1,0,'admin的任务','10001',1);
INSERT INTO DS_CATALOG VALUES (2, 1, 'test2', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (3, 1, 'test3', '10001', 2);      
INSERT INTO DS_CATALOG VALUES (4, 1, 'test4', '10001', 3);      
INSERT INTO DS_CATALOG VALUES (5, 1, 'test5', '10001', 4);      
INSERT INTO DS_CATALOG VALUES (6, 1, 'test6', '10001', 5);      
INSERT INTO DS_CATALOG VALUES (7, 1, 'test7', '10001', 6);      
INSERT INTO DS_CATALOG VALUES (8, 2, 'test8', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (9, 3, 'test9', '10001', 1);      
INSERT INTO DS_CATALOG VALUES (10, 2, 'test10', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (11, 3, 'test11', '10001', 2);    
INSERT INTO DS_CATALOG VALUES (12, 6, 'test12', '10001', 1);    
INSERT INTO DS_CATALOG VALUES (13, 7, 'test13', '10001', 1);   

3.2、查询

WITH RECURSIVE R AS (
    SELECT * FROM DS_CATALOG WHERE CATALOG_ID = 2 UNION ALL
    SELECT DS_CATALOG.* FROM DS_CATALOG, R WHERE DS_CATALOG.CATALOG_PARENT_ID = R.CATALOG_ID
) 
SELECT * FROM R ORDER BY CATALOG_ID;