1 SQL中的递归一般能干什么?
在SQL中写递归语句,一般可以干以下的事情(第2点可能常出现于一些SQL笔试题)
- 生成斐波那契数列;
- 补全两个日期之间的缺失日期;
- 树形查询。
2 举例
2.1 举例生成斐波那契数列
-- # 这一列是固定的
WITH RECURSIVE cte (n) AS
(
-- # 返回初始数据集 | 初始化条件
SELECT 1
UNION ALL
-- # 返回其它数据集 | n = n + 1 & n < 5
SELECT n + 1 FROM cte WHERE n < 5
)
select * from cte;
上述SQL的运行结果如下:
2.2 连续时间临时表
WITH RECURSIVE date_table AS (
SELECT to_date('2019-01-24') AS date
UNION ALL
SELECT date + '1 DAY'
FROM date_table
WHERE date < to_date('2030-01-01')
)
2.3 举例树型查询
在举例树型数据查询前,需要先建立一个组织的表和相关数据
-- 1 建表
CREATE TABLE organizational(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
);
-- 2 数据插入
INSERT INTO organizational VALUES
(333, "总经理", NULL),
(198, "副总1", 333),
(692, "副总2", 333),
(29, "主任1", 198),
(4610, "职员1", 29),
(72, "职员2", 29),
(123, "主任2", 692);
最后使用递归查询相关的组织关系如下
WITH RECURSIVE test(id, name, path)
AS
(
-- # 取出总经理的数据
SELECT id, name, CAST(id AS CHAR(200))
FROM organizational WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM test AS ep JOIN organizational AS e ON ep.id = e.manager_id
)
SELECT * FROM test ORDER BY path;
参考:
https://zhuanlan.zhihu.com/p/162006712
https://blog.csdn.net/weixin_30714077/article/details/113319959
https://blog.csdn.net/weixin_46631038/article/details/120996783