服务器之家

服务器之家 > 正文

MySQL 查询树结构方式

时间:2022-01-24 17:43     来源/作者:弦上的梦

mysql 查询树结构

1. 关于树结构

MySQL 查询树结构方式

此类结构的数据,通常需要表结构中含有id 、parentid等自关联字段,有时为了提高查询效率还可增加更多冗余字段,如index,index的值为所有父级目录的id字符串集合。

关于树结构数据的组装,常见的写法是在程序中通过递归的方式去构建出一颗完整的树,单纯通过sql的方式其实并不常用,下面分别给出两种方式的例子。

2. mysql自定义函数的方式

什么是mysql自定义函数:聚合函数,日期函数之类的都是mysql的函数,此处我们定义的函数可同他们一样使用,不过只能在定义的数据库中使用,自定义函数和存储过程类似,不同的是,函数只会返回一个值,不允许返回一个结果集。

2.1 创建测试数据

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table `tree`  (
  `id` bigint(11) not null,
  `pid` bigint(11) null default null,
  `name` varchar(255) character set utf8 collate utf8_general_ci null default null,
  primary key (`id`) using btree
) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic;
insert into `tree` values (1, 0, '中国');
insert into `tree` values (2, 1, '四川省');
insert into `tree` values (3, 2, '成都市');
insert into `tree` values (4, 3, '武侯区');
insert into `tree` values (5, 4, '红牌楼');
insert into `tree` values (6, 1, '广东省');
insert into `tree` values (7, 1, '浙江省');
insert into `tree` values (8, 6, '广州市');

2.2 获取 某节点下所有子节点

?
1
2
3
4
5
6
7
8
9
10
11
12
13
create function `get_child_node`(rootid varchar(100))  
returns varchar(2000) 
begin  
declare str varchar(2000); 
declare cid varchar(100);  
set str = '$';  
set cid = rootid;  
while cid is not null do  
    set str = concat(str, ',', cid);  
    select group_concat(id) into cid from tree where find_in_set(pid, cid);  
end while;  
return str;  
end

调用自定义函数

?
1
select * from tree where find_in_set(id, get_child_node(2));

MySQL 查询树结构方式

2.3 获取 某节点的所有父节点

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create function `get_parent_node`(rootid varchar(100))  
returns varchar(1000)  
begin  
declare fid varchar(100) default '';  
declare str varchar(1000) default rootid;  
  
while rootid is not null do  
    set fid =(select pid from tree where id = rootid);  
    if fid is not null then  
        set str = concat(str, ',', fid);  
        set rootid = fid;  
    else  
        set rootid = fid;  
    end if;  
end while;  
return str; 
end

调用自定义函数

?
1
select * from tree where find_in_set(id, get_parent_node(5));

MySQL 查询树结构方式

3. oracle数据库的方式

只需要使用start with connect by prior语句即可完成递归的树查询,详情请自己查阅相关资料。

4. 程序代码递归的方式构建树

这里我就不给出完整代码了,递归的方式很简单,就是先查出所有树节点,然后通过一个treenode类中的add方法递归把所有子节点给加进来。核心代码如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
public class treenodedto {
    
    private string id;
    private string parentid;
    private string name;
    private list<treenodedto> children = new arraylist<>();
    public void add(treenodedto node) {
        if ("0".equals(node.parentid)) {
            this.children.add(node);
        } else if (node.parentid.equals(this.id)) {
            this.children.add(node);
        } else {
            //递归调用add()添加子节点
            for (treenodedto tmp_node : children) {
                tmp_node.add(node);
            }
        }
    }
 }

5. 通过hashmap,只需要遍历一次

就可以完成树的生成:五星推荐

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
list<treenodedto> list = dbmapper.getnodelist();
arraylist<treenodedto> rootnodes = new arraylist<>();
map<integer, treenodedto> map = new hashmap<>();
for (treenodedto node :list) {
    map.put(node.getid(), node);
    integer parentid = node.getparentid();
    // 判断是否有父节点 (没有父节点本身就是个父菜单)
    if (parentid.equals('0')){
        rootnodes.add(node);
        // 找出不是父级菜单的且集合中包括其父菜单id
    } else if (map.containskey(parentid)){
        map.get(parentid).getchildren().add(node);
    }
}

mysql 查询带树状结构的信息

在oracle中有函数应用直接能够查询出树状的树状结构信息,例如有下面树状结构的组织成员架构,那么如果我们想查其中一个节点下的所有节点信息

在oracle中可以直接用下面的语法可以进行直接查询

?
1
start with connect by prior

但是在mysql中是没有这个语法的

而如果你也是想要查询这样的数据结构信息该怎么做呢?我们可以自定义函数。我们将上面的信息初始化信息进数据库中。首先先创建一张表用于存储这些信息,id为存储自身的id信息,parent_id存储父id信息

?
1
2
3
4
5
create table `company_inf` (
  `id` varchar(32) collate utf8mb4_unicode_ci default null,
  `name` varchar(255) collate utf8mb4_unicode_ci default null,
  `parent_id` varchar(32) collate utf8mb4_unicode_ci default null
)

然后将图中的信息初始化表中

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
insert into company_inf values ('1','总经理王大麻子','1');
insert into company_inf values ('2','研发部经理刘大瘸子','1');
insert into company_inf values ('3','销售部经理马二愣子','1');
insert into company_inf values ('4','财务部经理赵三驼子','1');
insert into company_inf values ('5','秘书员工j','1');
insert into company_inf values ('6','研发一组组长吴大棒槌','2');
insert into company_inf values ('7','研发二组组长郑老六','2');
insert into company_inf values ('8','销售人员g','3');
insert into company_inf values ('9','销售人员h','3');
insert into company_inf values ('10','财务人员i','4');
insert into company_inf values ('11','开发人员a','6');
insert into company_inf values ('12','开发人员b','6');
insert into company_inf values ('13','开发人员c','6');
insert into company_inf values ('14','开发人员d','7');
insert into company_inf values ('15','开发人员e','7');
insert into company_inf values ('16','开发人员f','7');

例如我们想要查询研发部门经理刘大瘸子下的所有员工,在oracle中我们可以这样写

?
1
2
3
4
select *
from t_portal_authority
start with id='1'
connect by prior id = parent_id

而在mysql中我们需要下面这样自定义函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create function getchild(parentid varchar(1000))
returns varchar(1000)
begin
    declare otemp varchar(1000);
    declare otempchild varchar(1000);
    set otemp = '';
    set otempchild =parentid;
    while otempchild is not null do
        if otemp != '' then
            set otemp = concat(otemp,',',otempchild);
        else
            set otemp = otempchild;
        end if;
        select group_concat(id) into otempchild from company_inf where parentid<>id and find_in_set(parent_id,otempchild)>0;
    end while;
return otemp;
end

然后这样查询即可

?
1
select * from company_inf where find_in_set(id,getchild('2'));

此时查看查询出来的信息就是刘大瘸子下所有的员工信息了

以上为个人经验,希望能给大家一个参考,也希望大家多多支持服务器之家。

原文链接:https://blog.csdn.net/qq_34997906/article/details/94007556

标签:

相关文章

热门资讯

蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整
蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整 2021-08-24
yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
2021年耽改剧名单 2021要播出的59部耽改剧列表
2021年耽改剧名单 2021要播出的59部耽改剧列表 2021-03-05
返回顶部