前言：

之前项目一直用的Oracle数据库，在查询层次（树状）结构数据时，有CONNECT BY语法使用，后面转到Mysql时，想知道是否有类似的查询手段，在网上查了资料后遂在此记录一下。

CONNECT BY

语法：

SELECT select_list
FROM table_expression
[ WHERE ... ]
[ START WITH start_expression ]
CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
[ ORDER SIBLINGS BY column1 [ ASC | DESC ] [, column2 [ ASC | DESC ] ] ... ]
[ GROUP BY ... ]
[ HAVING ... ]
...

举例：

SELECT level,id,parent_id,name
FROM
  treeTable
  START WITH id = 1
  CONNECT BY prior id = parent_id
ORDER BY
  level

## level列表示层级，在treeTable中不存在该列，是查询带出来的

该语法在Mysql中不支持，仅在Oracle中使用。

Common table expression (CTE)

来源Wiki：支持的数据库
Teradata（从版本14开始），DB2，Informix（从版本14.1开始），Firebird（从版本2.1开始）， Microsoft SQL Server（从版本2005开始），Oracle（从以下版本开始递归）均支持公用表表达式11g第2版），PostgreSQL（版本8.4），MariaDB（版本10.2），MySQL（版本8.0），SQLite（版本3.8.3），HyperSQL，Informix（版本14.10）， Google BigQuery，Sybase（版本开始） 9），Vertica的，H2（实验）等等

示例：

with recursive cte (_level, id, name, parent_id) as (
  select     0 as _level,
	     id,
             name,
             parent_id
  from       treeTable
  where      parent_id is null 
  union all
  select     
             cte._level + 1,
	     p.id,
             p.name,
             p.parent_id
  from       treeTable p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

Wiki上关于阶乘的示例：

WITH RECURSIVE temp (n, fact) AS 
(SELECT 1, 1 -- Initial Subquery
  UNION ALL 
 SELECT n+1, (n+1)*fact FROM temp -- Recursive Subquery 
        WHERE n < 9)
SELECT * FROM temp;

结果：

Q.E.D.