玩转MySQL:深入理解递归函数的技巧与应用
最编程
2024-02-20 22:16:03
...
1. 前置学习 @符号
-
@
后接变量名,用以定义一个变量,该变量的有效期为语句级,即再一次执行中始终有效,基本示例如下:
-- 由于通常情况下=被认为是等于比较运算符,因此赋值运算符一般使用:= SELECT @lt:=1, @lt:=@lt+1, @lt:=@lt+1;
1 2 3
-- 由于tmp只有一行,当这两表进行笛卡尔积链接时,结果集实际上等同于增加了一列,而由于r变量的特性,每行都在原值的基础上在进行增加操作 SELECT a.BATCHNO, a.YEAR, @r:=@r+1 FROM m1 a, (SELECT @r:=0) tmp;
例子:连续出现的数字
输入:
Logs 表:
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
输出:
Result 表:
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解释:1 是唯一连续出现至少三次的数字。
#首先遍历一遍整张表,找出每个数字的连续重复次数
#具体方法为:
#初始化两个变量,一个为pre,记录上一个数字;一个为count,记录上一个数字已经连续出现的次数。
#然后调用if()函数,如果pre和当前行数字相同,count加1极为连续出现的次数;如果不同,意味着重新开始一个数字,count重新从1开始。
#最后,将当前的Num数字赋值给pre,开始下一行扫描。
select
Num, #当前的Num 数字
if(@pre=Num,@count := @count+1,@count := 1) as nums, #判断 和 计数
@pre:=Num #将当前Num赋值给pre
from Logs as l ,
(select @pre:= null,@count:=1) as pc #这里需要别名
#上面这段代码执行结果就是一张三列为Num,count as nums,pre的表。
#②将上面表的结果中,重复次数大于等于3的数字选出,再去重即为连续至少出现三次的数字。
select
distinct Num as ConsecutiveNums
from
(select Num,
if(@pre=Num,@count := @count+1,@count := 1) as nums,
@pre:=Num
from Logs as l ,
(select @pre:= null,@count:=1) as pc
) as n
where nums >=3;
#注意:pre初始值最好不要赋值为一个数字,因为不确定赋值的数字是否会出现在测试表中。
# 表的别名一个不能少
2. find_in_set()介紹
find_in_set函数使用
FIND_IN_SET(str,strlist)
str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8)
精确匹配 ,分隔的数据
mysql中find_in_set()函数的使用 - 平凡希 - 博客园
3. MySQL function函数 - 递归函数使用
Mysql 根据id查所有父级或子级 - 锐洋智能 - 博客园
mysql递归查询,mysql中从子类ID查询所有父类(做无限分类经常用到)
由于mysql 不支持类似 oracle with ...connect的 递归查询语法
之前一直以为类似的查询要么用存储过程要么只能用程序写递归查询.
现在发现原来一条sql语句也是可以搞定的
先来看数据表的结构如下:
id name parent_id
---------------------------
1 Home 0
2 About 1
3 Contact 1
4 Legal 2
5 Privacy 4
6 Products 1
7 Support 1
我要的要求是根据一个分类ID(这个分类ID可能是一个子分类),得到所有的父分类,下面是相应的SQL:
SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, #@r是id,下一个递归的id=当前的parent_id,下一个递归的id作为新的parent_id
@l := @l + 1 AS lvl
FROM
(SELECT @r := 5, @l := 0) vars, #定义变量@r,@l
table1 h
WHERE @r <> 0) T1 #递归终止条件是 id==0
JOIN table1 T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
代码@r := 5标示查询id为5的所有父类。结果如下
1, ‘Home’
2, ‘About’
4, ‘Legal’
5, ‘Privacy’
单个递归
### 声明头文件 单个
CREATE DEFINER=`user_name` FUNCTION `getFromTree`(
selectIds VARCHAR (1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, ### 入参是 123 的形式,递归找出所有路径上的父节点
workId VARCHAR (100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ### 单个其他条件
) RETURNS varchar(1000) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
BEGIN
DECLARE selectId VARCHAR (50); ### 单个selectId
DECLARE result VARCHAR (1000); ### 单个selectId找到的父节点
SELECT
GROUP_CONCAT(T2.result_id) into result ### 将查询的结果放入临时变量,GROUP_CONCAT是 追加,
FROM
(
SELECT
@r AS _id,
(
SELECT
@r := parent_id
FROM
work_tree
WHERE
id = _id
) AS parent_id
FROM
(SELECT @r := selectId) vars, ### 定义变量@r
work_tree
WHERE
@r <> 0 ### 父节点不为0
) T1
JOIN work_tree T2 ON T1._id = T2.id
WHERE
T2.workId = workId and (T2.type !=2 or T2.parent_id = 0);
RETURN result;
END
推荐阅读
-
轻松入门 MySQL:深入了解MySQL聚合函数、在实际库存项目中的应用和技巧(8) - CSDN博客
-
深入理解 MySQL 性能测试与 sysbench 工具的实战应用
-
玩转Kotlin性能测试:JMH入门指南一 - 测试基础" "深入理解JMH在Kotlin中的应用:基准测试实战解析" "轻松实践Kotlin基准测试:JMH工具详解与实例总结
-
深入理解Linux指令 | tar命令实操指南:打包与解压文件的艺术,广泛应用于备份与文件压缩——第二部分,操作技巧解析
-
玩转Python:深入理解进程与线程的区别与应用
-
深入理解指针技巧:轻松探索函数指针、函数指针数组与指向该数组的指针实战指南
-
深入理解并掌握Array.reduce()函数的详尽指南与实用高级技巧
-
玩转JS:理解函数的this、闭包与递归概念
-
玩转MySQL:深入理解递归函数的技巧与应用
-
玩转C++:理解类与对象 - 多态详解:基本语法、核心原理、虚函数与抽象类,以及虚析构与纯虚析构的实际应用