需求:从B01表里查出id='e066f33dabe655b650bc3031e43cb14b’的结果集,然后查出id等于结果集的parentId的所有结果集
B00表示id,B0101表示name,parentId表示他的父节点的id;
一般情况下,用子查询即可做到,sql语句如下:
SELECT B00,B0101 FROM B01 WHERE B00 = ‘e066f33dabe655b650bc3031e43cb14b’
union all
SELECT B00,B0101 FROM B01 WHERE PARENTID IN(SELECT B00 FROM B01 WHERE B00 = ‘e066f33dabe655b650bc3031e43cb14b’)
但是,如果存在多层的层级,就需要写很多重复的sql,所以用到了递归查询,with as
WITH a(B00) AS
(
SELECT one.B00 FROM B01 one WHERE one.B00 = ‘e066f33dabe655b650bc3031e43cb14b’
UNION ALL
SELECT two.B00 FROM a INNER JOIN B01 two on two.PARENTID = a.B00
)
SELECT * FROM a
具体在动态sql中的使用如下:
CTE的语法:
[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,n ] ) ]
AS
( CTE_query_definition )