请选择 进入手机版 | 继续访问电脑版

湖南新梦想

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
查看: 365|回复: 0

MySQL、Oracle、达梦、PostgreSQL四种数据库的递归查询

[复制链接]

2775

主题

3174

帖子

1万

积分

论坛元老

Rank: 8Rank: 8

积分
11260
发表于 2021-3-24 14:33:33 | 显示全部楼层 |阅读模式
我在项目研发过程中,到设计表的这一环节,经常会遇到树结构的设计,这个查询让我非常头疼,以前的处理都是写手递归函数,这种方式虽然可以解决,但是很不方便。今天我们依赖[url=]数据库[/url]处理它。
  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;

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|小黑屋|湖南新梦想 ( 湘ICP备18019834号-2 )