专栏首页文渊之博mysql 层级结构查询

mysql 层级结构查询

描述:最近遇到了一个问题,在mysql中如何完成节点下的所有节点或节点上的所有父节点的查询? 在Oracle中我们知道有一个Hierarchical Queries可以通过CONNECT BY来查询,但是,在MySQL中还没有对应的函数!!! 下面给出一个function来完成的方法 下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

好记性不如烂笔头 下面给出一个function来完成的方法

下面是sql脚本,想要运行的直接赋值粘贴进数据库即可。

创建表treenodes(可以根据需要进行更改)


– Table structure for treenodes


DROP TABLE IF EXISTS treenodes; CREATE TABLE treenodes ( id int(11) NOT NULL, nodename varchar(20) DEFAULT NULL, pid int(11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


– Table structure for treenodes


插入几条数据


– Records of treenodes


INSERT INTO treenodes VALUES (‘1’, ‘A’, ‘0’);
INSERT INTO treenodes VALUES (‘2’, ‘B’, ‘1’);
INSERT INTO treenodes VALUES (‘3’, ‘C’, ‘1’);
INSERT INTO treenodes VALUES (‘4’, ‘D’, ‘2’);
INSERT INTO treenodes VALUES (‘5’, ‘E’, ‘2’);
INSERT INTO treenodes VALUES (‘6’, ‘F’, ‘3’);
INSERT INTO treenodes VALUES (‘7’, ‘G’, ‘6’);
INSERT INTO treenodes VALUES (‘8’, ‘H’, ‘0’);
INSERT INTO treenodes VALUES (‘9’, ‘I’, ‘8’);
INSERT INTO treenodes VALUES (‘10’, ‘J’, ‘8’);
INSERT INTO treenodes VALUES (‘11’, ‘K’, ‘8’);
INSERT INTO treenodes VALUES (‘12’, ‘L’, ‘9’);
INSERT INTO treenodes VALUES (‘13’, ‘M’, ‘9’);
INSERT INTO treenodes VALUES (‘14’, ‘N’, ‘12’);
INSERT INTO treenodes VALUES (‘15’, ‘O’, ‘12’);
INSERT INTO treenodes VALUES (‘16’, ‘P’, ‘15’);
INSERT INTO treenodes VALUES (‘17’, ‘Q’, ‘15’); 

把下面的语句直接粘贴进命令行执行即可(注意修改传入的参数,默认rootId,表明默认treenodes)

根据传入id查询所有父节点的id

delimiter // 
CREATE FUNCTION `getParList`(rootId INT)
RETURNS varchar(1000) 
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempPar VARCHAR(1000); 
    SET sTemp = ''; 
    SET sTempPar =rootId; 

    #循环递归
    WHILE sTempPar is not null DO 
        #判断是否是第一个,不加的话第一个会为空
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        SET sTemp = concat(sTemp,',',sTempPar); 
        SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0; 
    END WHILE; 

RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getParList(15)); 结果:

根据传入id查询所有子节点的id

delimiter // 
CREATE FUNCTION `getChildList`(rootId INT)
RETURNS varchar(1000) 

BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempChd VARCHAR(1000);

    SET sTemp = '$';
    SET sTempChd =cast(rootId as CHAR);

    WHILE sTempChd is not null DO
        SET sTemp = concat(sTemp,',',sTempChd);
        SELECT group_concat(id) INTO sTempChd FROM  treeNodes where FIND_IN_SET(pid,sTempChd)>0;
    END WHILE;
    RETURN sTemp; 
END
//

执行命令

select * from treenodes where FIND_IN_SET(id,getChildList(7)); 结果:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 探索SQL Server元数据(二)

      上一篇中,我介绍了SQL Server 允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例...

    用户1217611
  • Hive简介

    Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。

    用户1217611
  • xpath语法大全

    XPath 节点 ---- XPath 术语 节点 在 XPath 中,有七种类型的节点:元素、属性、文本、命名空间、处理指令、注释以及文档(根)节点。XML...

    用户1217611
  • Spring对象解析及注册(二)

    前面对Spring解析对象的整体做些整理,下面进出核心方法registerBeanDefinitions

    OPice
  • 高可用架构设计(2) -hystrix要解决的分布式系统可用性问题以及其设计原则

    高可用性这个topic,然后咱们会用几讲的时间来讲解一下如何用hystrix,来构建高可用的服务的架构

    JavaEdge
  • silverlight3的"伪"3D续--图片横向轮换

    上一篇里,已经知道了“伪3D”是怎么回事,今天结合以前的做的图片广告轮换,又弄了一个图片切换的小东东,不知道以后有什么用,先贴在这里备份: 效果图: ? 因为s...

    菩提树下的杨过
  • Android 向FTP服务器上传以及下载文件

    首先我们要用到commons-net-3.3.jar包 可以去网上下载 找不到可私信我 好了,下面直接上代码 封装了一个类出来可直接使用

    longzeqiu
  • C++创建对象的三种方式

      第一种和第二种没什么区别,一个隐式调用,一个显式调用,两者都是在进程虚拟地址空间中的栈中分配内存,而第三种使用了new,在堆中分配了内存,而栈中内存的分配和...

    战神伽罗
  • 数据安全实践之数据资产管理

    在企业安全建设中,资产管理是很多安全工作的基础。而数据资产管理可以帮助我们更准确的发现安全风险,执行更有效的控制措施,在数据安全体系化建设中也有着举足轻重的作用...

    FB客服
  • 开源库Magicodes.ECharts使用教程

    博客使用Word发博,发布后,排版会出现很多问题,一一修正工作量极大,敬请谅解。可加群获取原始文档。

    雪雁-心莱科技

扫码关注云+社区

领取腾讯云代金券