mysql 递归查询父节点 和子节点
2023-09-11 14:18:37 时间
查父集合
--drop FUNCTION `getParentList` CREATE FUNCTION `getParentList`(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 parentid FROM treeNodes 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
查询语句
select getParentList('001001001001001'); select * from sbkfwh where FIND_IN_SET(id,getParentList('001001001001002'))
查子集合
--drop FUNCTION `getChildList` CREATE FUNCTION `getChildList`(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 treeNodes where FIND_IN_SET(parentid, cid) > 0; END WHILE; RETURN str; END
查询语句
select getParentList('001001001'); select * from sbkfwh where FIND_IN_SET(id,getChildList('001001001'))
相关文章
- Error connecting to database [Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)]
- Mysql授权允许远程访问解决Navicat for MySQL连接mysql提示客户端不支持服务器请求的身份验证协议;考虑升级MySQL客户端
- 【Mysql 学习】mysql 的使用入门
- MySQL Cluster在线添加数据节点
- Linux/CentOS 服务安装/卸载,开机启动chkconfig命令详解|如何让MySQL、Apache开机启动?
- 【MySQL】MySQL多实例开机自动重启
- 【Mysql安装】Mac下安装mysql
- 关于mysql函数GROUP_CONCAT
- mysql function动态执行不同sql语句
- mysql之show engine innodb status解读(转)
- 重新整理 mysql 基础篇————— 介绍mysql日志[二]
- Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)
- MYSQL随机抽取查询 MySQL Order By Rand()效率问题
- [MySQL] MySQL x64 下载地址
- 使用mysql-connector-python操作MYSQL数据库
- MySQL高性能优化系列
- PHP + Smarty + MySQL
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
- MYSQL随机抽取查询 MySQL Order By Rand()效率问题
- MYSQL避免全表扫描__如何查看sql查询是否用到索引(mysql)
- Python MySQL - mysql-connector 驱动
- MySQL 聚簇索引和非聚簇索引 & mysql 索引为啥用b 树
- MySQL Study之--Mysql无法启动“mysql.host”
- Mysql的性能优化
- MySQL技术内幕读书笔记(一)——Mysql体系结构和存储引擎
- mysql select into outfile默认文件保存路径是C:ProgramDataMySQLMySQL Server 8.0Data
- Mysql 表分区分类
- Mysql之mysql工具