`
Towan
  • 浏览: 7915 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

mysql数据库实现类似oracle connect by prior的查询

 
阅读更多
--注下面的语句用到了表
DROP TABLE IF EXISTS `a_sys_org`;
CREATE TABLE `a_sys_org` (
`org_id` varchar(32) NOT NULL COMMENT '机构号',
`org_name` varchar(256) NOT NULL COMMENT '机构名称',
`parent_org_id` varchar(32) NOT NULL COMMENT '父机构号',
PRIMARY KEY (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='系统机构表';


-- 方法1,函数实现
-- 注意 SELECT getChildOrg('00010');
-- 最大支持字符串连接102400

DROP FUNCTION IF EXISTS getChildidList;
CREATE FUNCTION getChildidList(rootId VARCHAR(32))
RETURNS text
BEGIN
DECLARE sTemp text;
DECLARE sTempChd text;
SET@@group_concat_max_len = 102400;
SET sTemp = '$';
SET sTempChd = rootId;

WHILE sTempChd IS NOT NULL DO
SET sTemp = concat(sTemp, ',', sTempChd);
SELECT
group_concat(org_id) INTO sTempChd
FROM
a_sys_org
WHERE
FIND_IN_SET(parent_org_id, sTempChd) > 0;
END WHILE;
SET@@group_concat_max_len = 1024;
RETURN SUBSTRING(sTemp,3);
END

-- 方法2,存储过程+中间表
-- 存储过程实现mysql递归查询,类似oracle start with connect by prior
-- 注意 call getChildOrg('00010');
-- 递归有深度限制,最大是255层

DROP PROCEDURE IF EXISTS getChildOrg;
CREATE PROCEDURE getChildOrg (IN rootid VARCHAR(32))
BEGIN
DECLARE _level_var INT;

DROP TABLE IF EXISTS temp_child_list;
CREATE TABLE temp_child_list (
_id VARCHAR(32),
_level INT
);
SET _level_var = 0;
INSERT INTO temp_child_list (_id, _level) VALUE(rootid,_level_var);
SET _level_var = _level_var + 1;
INSERT INTO temp_child_list (_id, _level)
SELECT
org_id,
_level_var
FROM
a_sys_org
WHERE
parent_org_id = rootid;
WHILE ROW_COUNT() > 0
DO
SET _level_var = _level_var + 1;
INSERT INTO temp_child_list SELECT
a.org_id,
_level_var
FROM
a_sys_org a,
temp_child_list b
WHERE
a.parent_org_id = b._id
AND b._level = _level_var - 1;
END WHILE;
SELECT _id FROM temp_child_list ORDER BY _level;
DROP TABLE IF EXISTS temp_child_list;
END;

-- 方法3,存储过程+临时表
-- 存储过程实现mysql递归查询,类似oracle start with connect by prior
-- 注意 call getChildOrg('00010');
-- 递归有深度限制,最大是255层
-- 此存储过程利用了临时表

DROP PROCEDURE IF EXISTS showChildList;
CREATE PROCEDURE showChildList (IN rootId VARCHAR(32))
BEGIN
SET@@max_sp_recursion_depth=99;
DROP TEMPORARY TABLE IF EXISTS temp_child_list;
CREATE TEMPORARY TABLE temp_child_list ( sno INT PRIMARY KEY auto_increment, _id VARCHAR (32), _depth INT );
-- 插入当前节点
INSERT INTO temp_child_list (_id, _depth) VALUES (rootId ,- 1);
-- 插入子节点
CALL createChildList (rootId, 0);
SELECT o.org_id FROM temp_child_list t, a_sys_org o WHERE t._id = o.org_id ORDER BY t.sno;
END;
DROP PROCEDURE IF EXISTS createChildList;
CREATE PROCEDURE createChildList ( IN rootId VARCHAR (32), IN depth_var INT )
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b VARCHAR (32);
DECLARE cur1 CURSOR FOR SELECT org_id FROM a_sys_org WHERE parent_org_id = rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
INSERT INTO temp_child_list (_id, _depth) SELECT org_id, depth_var FROM a_sys_org WHERE parent_org_id = rootId;
OPEN cur1;
FETCH cur1 INTO b;
WHILE done = 0 DO CALL createChildList (b, depth_var + 1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
分享到:
评论

相关推荐

    Oracle start with.connect by prior子句实现递归查询

    ### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中,`Start With.Connect By Prior`子句是执行递归查询的一种强大工具,主要用于处理层次结构数据。这类数据通常存在于组织...

    ORACLE查询树型关系(connect_by_prior_start_with)

    ORACLE 查询树型关系(connect_by_prior_start_with) Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到...

    MySQL多种递归查询方法.docx

    在Oracle数据库中,递归查询可以通过`START WITH CONNECT BY PRIOR`语句实现。此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM ...

    Oracle递归查询start with connect by prior的用法

    connect by递归查询基本语法是: select 1 from 表格 start with … connect by prior id = pId start with:表示以什么为根节点,不加限制可以写1=1,要以id为123的节点为根节点,就写为start with id =123 ...

    递归查询菜单树,支持mysql,oracle

    这些技术通常提供API接口,将数据库查询结果转化为JSON格式,供前端渲染。 总的来说,递归查询菜单树是数据库操作的一个常见应用场景,它涉及到数据库设计、SQL查询技巧以及前后端的数据交换。理解并掌握这些知识点...

    在ORACLE、MSSQL、MYSQL中树结构表递归查询的实现.pdf

    ORACLE、MSSQL(Microsoft SQL Server)、MYSQL是三大主流数据库系统,它们各自提供了不同的方式来实现递归查询。 首先,我们来看ORACLE数据库中的递归查询。ORACLE通过CONNECT BY子句来实现递归查询。CONNECT BY...

    一种MySQL数据库SQL递归查询的研究与实现.pdf

    CONNECT BY PRIOR`子句直接支持递归查询,而SQL Server 2005及以上版本则利用公共表表达式(CTE)的递归调用来实现。POSTgreSQL同样支持CTE子查询进行递归。这些数据库允许在SQL语句中直接对子查询进行递归调用,...

    数据库设计之递归树查询

    本文将深入探讨如何通过递归查询来解决这类问题,并着重讲解使用`WITH`语句来实现递归查询的方法,适用于多种数据库系统,如MySQL、PostgreSQL、SQL Server等。 一、理解递归查询 递归查询是一种在数据库中遍历层级...

    oracle mysql SQL server分页

    本文将详细介绍Oracle、MySQL以及SQL Server这三种常用数据库管理系统中的分页查询实现方法。 #### 一、Oracle 分页查询 在Oracle中实现分页查询主要依靠`ROWNUM`伪列,这是一种非常简单但功能强大的机制。下面将...

    解决Oracle没有WM_CONCAT函数.zip

    在Oracle数据库中,WM_CONCAT是一个非常实用的聚合函数,用于将一组字符串连接成一个单一的字符串,类似于SQL Server中的STRING_AGG或MySQL中的GROUP_CONCAT。然而,Oracle官方并没有提供这个函数,它是一个第三方...

    SQL语句 递归

    在Oracle中,递归查询主要通过`START WITH`和`CONNECT BY PRIOR`子句实现。 ### 二、递归查询的基本语法 递归查询的基本语法如下: ```sql SELECT column_name(s) FROM table_name START WITH condition CONNECT ...

    Mysql树形递归查询的实现方法

    CONNECT BY`的递归查询语法,我们需要采取其他策略来实现类似的功能。 在Oracle中,`START WITH`指定查询的起始点,而`CONNECT BY`则用于定义层次结构,`PRIOR`关键字用来链接当前行与它的父行。例如,如果有一个`...

    mysql生成省市县行政区划SQL语句

    Oracle的语法与MySQL类似,但在处理层级数据时,可能需要利用Hierarchical Query(层级查询)功能。例如,使用CONNECT BY子句来查询层级关系: ```sql SELECT level, sys_connect_by_path(name, '/') AS 路径 FROM ...

    树形查询带记录数量

    在进行树形查询时,我们通常会用到递归SQL查询或者利用数据库提供的特定功能,如Oracle的CONNECT BY,MySQL的WITH RECURSIVE,或者SQL Server的Hierarchical Queries。这些语法允许我们构建一个层次结构,从根节点...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    SQL(Structured Query Language)结构化查询语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的扩展名。  SQL语言主要包含5个部分  数据定义...

    delphi连接数据库高级操作

    通过TFDConnection、TFDQuery、TFDTable等组件,可以实现数据库的连接、查询和数据操作。使用FireDAC的优点在于其强大的数据类型转换能力,跨平台特性,以及对异步操作的支持。 3. 数据库连接字符串:无论是使用ADO...

    总结问题集合

    `Connect By Prior`是Oracle数据库中的一个特殊查询语句,用于检索具有层次结构的数据。它允许你定义节点之间的父/子关系,并按层次顺序检索结果。例如: ```sql SELECT * FROM TBL_TEST START WITH id = 10 ...

    SQL袖珍参考手册(第3版)

    CONNECT BY是Oracle数据库中特有的语法,用于创建层次结构查询。它可以基于表中的父子关系来生成树状结构的数据。例如: ```sql SELECT LEVEL, empno, ename FROM emp START WITH mgr IS NULL CONNECT BY PRIOR ...

Global site tag (gtag.js) - Google Analytics