mysql 递归查询
来源:互联网 发布:企业站源码 编辑:程序博客网 时间:2024/06/05 02:05
相关函数
String Operators
FIND_IN_SET()
Return the index position of the first argument within the second argument
CONCAT()
Return concatenated string
Aggregate (GROUP BY) Functions
GROUP_CONCAT() Return a concatenated string
mysql> SELECT student_name,GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
Or:
mysql> SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name
The default separator between values in a group is comma (,).
查询所有子节点,包括当前节点,用,分隔,,限制循环层级最大为10,保证始终可退出递归
CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(rootId bigint) RETURNS varchar(2000) CHARSET utf8
BEGIN
declare str varchar(2000);
declare cid varchar(2000);
declare level_count int;
set str = '';
set cid = cast(rootId as char);
set level_count = 0;
while cid is not null and level_count<10 do
set str = concat(str,',',cid);
select group_concat(id) into cid from product_category where find_in_set(parent_id,cid)>0;
set level_count = level_count+1;
end while;
return substring(str,2);
END
CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(nodeId bigint) RETURNS varchar(2000) CHARSET utf8
BEGIN
declare pid bigint default null;
declare str varchar(2000) default nodeId;
while (nodeId is not null)
do
set pid=(select parent_id from product_category where id=nodeId);
if pid is not null then
set str = concat(str,',',pid);
end if;
set nodeId=pid;
end while;
RETURN str;
END
阅读全文
0 0
- mysql递归查询
- Mysql递归查询
- MySQL递归层次查询
- MySql递归层次查询
- mysql递归查询。
- MySql递归查询函数
- Mysql递归查询
- mySql递归查询函数
- mysql递归查询
- MySQL递归查询
- mysql递归查询
- mybatis+mysql递归查询
- mysql-递归查询
- mysql递归查询组织机构
- mysql关于递归查询
- MySQL 递归查询
- mysql 递归查询
- mysql 递归查询
- How to overlay an icon over existing shell objects in 3 easy steps
- Python 省市分词提取
- 无法生成.config
- Android EditText控件使用
- 带你测试对比深度学习框架!TensorFlow,Keras,PyTorch...哪家强?(附数据集)
- mysql 递归查询
- 图片的切换
- android note
- 大数据可视化--全球分布散点图
- 高斯消元法(二):高斯消元法原理
- 关于linux的export命令和shell变量的生命周期,环境变
- H盘显示由于IO设备错误,无法运行此项请求要如何办啊
- 自执行函数
- gstreamer插件简介