MySQL8公用表表达式

公用表表达式
https://dev.mysql.com/doc/refman/8.0/en/with.html

普通公用表表达式

WITH  id
AS (SELECT DISTINCT  id FROM sys_process)
 SELECT *
 FROM sys_process d JOIN  id e
 ON d.pid = e.id;

---

递归公用表表达式

WITH RECURSIVE cte 
AS 
(
SELECT *,1 AS n FROM sys_process WHERE pid = 0 -- 种子查询，找到第一代领导
UNION ALL
SELECT a.*,n+1 FROM sys_process AS a JOIN cte
ON (a.pid = cte.id) -- 递归查询，找出以递归公用表表达式的人为领导的人
)
SELECT * FROM cte WHERE n >= 0; 

查询的结果多一列，显示层数，第一层为1，编写n的条件可以显示对应的层数数据。