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