WITH AS实现查找表中指定节点的所有子节点

要实现的节点树结构如图。要求能根据“节点1”获取该节点下所有子节点

表结构如下

WITH AS 语句如下

WITH tb AS(<span style="font-family: Arial, Helvetica, sans-serif;">select * from node where nodeId=1 union all select node.nodeId,node.nodeName,node.parentId from node.tb where node.parentId=tb.nodeId)select * from tb</span>

WITH AS 是CTE（公用表表达式）的语法

[WITH<common_table_expression>[,n]]

<common_table_expression>::=

expression_name[(column_name[,n])]

AS

(CTE_query_definition)

CTE可以用来优化子查询，例如：

select * from (select * from table)tb

可以写成

WITH tb AS

(select * from table)

select * from tb;

需要注意的是CTE后面必须直接跟使用CTE的SQL语句，否则CTE会失效。