sql递归查询 | 您所在的位置:网站首页 › sql怎么降序查询 › sql递归查询 |
需求:从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 asWITH 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 [ WITH [ ,n ] ] ::= expression_name [ ( column_name [ ,n ] ) ] AS ( CTE_query_definition ) |
CopyRight 2018-2019 实验室设备网 版权所有 |