公用表表达式(CTE)

  |   0 评论   |   0 浏览

非递归 CTE

共用表表达式(CTE) 与派生表类似,但它的声明会放在查询块之前,而不是 FROM 子句中。

派生表

SELECT ... FROM (subquery) as derived, t1 ...

CTE

SELECT ... WITH derived AS (subquery) SELECT ... FROM derived, t1 ...

CTE 可能在 SELECT/UPDATE/DELETE 之前,包括 WITH derived AS (subquery) 的子查询,例如:

DELETE FROM t1 WHERE t1.a IN (SELECT b FROM derived);

括号中可以替换成 CTE

假设你想了解每年的工资较前一年的增长百分比,如果没有 CTE,你需要编写两个子查询,这两个子查询本质上是相同的,但是 MySQL 并不能识别处它们是相同的,这就会导致子查询被执行两次。

SELECT 
q1.year,
q2.year AS next_year,
q1.sum,
q2.sum AS next_sum,
100 * (q2.sum-q1.sum)/q1.sum AS pct
FROM (SELECT year(from_date) as year,sum(salary) as sum FROM salaries GROUP BY year) as q1,
(SELECT year(from_date) as year,sum(salary) as sum FROM salaries GROUP BY year) AS q2
WHERE q1.year = q2.year - 1;

如果使用非递归 CTE, 派生查询只执行一次并重用:

WITH CTE AS 
(SELECT year(from_date) AS year,SUM(salary) as sum FORM salaries GROUP BY year)
SELECT q1.year,q2.year as next_year,q1.sum,q2.sum as next_sum, 100 * (q2.sum-q1.sum)/q1.sum as pct FROM 
CTE AS q1,
CTE AS q2
WHERE q1.year = q2.year - 1;

递归 CTE

递归 CTE 是一种特殊的 CTE ,其子查询会引用自己的名字。 WITH 子句必须以 WITH RECURSIVE开头

递归 CTE 子查询包含两个部分: seed 查询喝 recusive 查询, 由 UNION [ALL]或 UNION DISTINCT 分割

seed SELECT 被执行一次以创建初始数据子集recursive SELECT 被重复执行以返回数据的子集,直到获得完整的结果集。 当迭代不会生成新行时,递归会停止

这对挖掘层次机构(父/子或部分/子部分)非常有用:

WITH RECURSIVE cte AS

(SELECT .... FROM table_name /* seed SELECT */

UNION ALL

SELECT ... FROM cte,table_name ) /* recursive query */

SELECT ... FROM cte;

假设要打印从 1 到 5 的所有数字:

WITH RECURSIVE cte (n) as
(
   select 1
	 UNION ALL
	 SELECT n + 1 from cte where n <=5
)

select * from cte

假设要执行分层数据遍历,以便为每个员工生成一个组织结构图(即从 CEO 到每个员工的路径),也可以使用递归 CTE!

创建带有 manager_id 的测试表

CREATE TABLE `employees_mgr`  (
  `id` int(11) NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `manager_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
)

插入示例数据:

INSERT INTO `employees_mgr` VALUES (29, 'Pedro', 198);
INSERT INTO `employees_mgr` VALUES (72, 'Pierre', 29);
INSERT INTO `employees_mgr` VALUES (123, 'Adil', 692);
INSERT INTO `employees_mgr` VALUES (198, 'John', 333);
INSERT INTO `employees_mgr` VALUES (333, 'Yasmina', NULL);
INSERT INTO `employees_mgr` VALUES (692, 'Tarek', 333);
INSERT INTO `employees_mgr` VALUES (4610, 'Sarah', 29);

执行递归 CTE

WITH RECURSIVE employee_paths (id,name,path)AS 
(
   SELECT id,name, CAST(id AS CHAR(200))
	 FROM employees_mgr
	 WHERE manager_id is NULL
	 UNION ALL
	 
	 SELECT e.id,e.name,CONCAT(ep.path, ',',e.id)
	 FROM employee_paths AS ep JOIN employees_mgr AS e
	 ON ep.id=e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;

产生以下结果


标题:公用表表达式(CTE)
作者:zh847707713
地址:http://lovehao.cn/articles/2020/06/08/1591608186457.html