我在项目研发过程中，到设计表的这一环节，经常会遇到树结构的设计，这个查询让我非常头疼，以前的处理都是写手递归函数，这种方式虽然可以解决，但是很不方便。今天我们依赖数据库处理它。
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与达梦一致）
2.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); |
2.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; |
本文内容不用于商业目的，如涉及知识产权问题，请权利人联系博为峰小编(021-64471599-8017)，我们将立即处理
