码农之家 - 递归
type: channel
link: http://blog.liyx.fun/index.php/tag/递归/
items: SQL语句递归查询 sqlserver oracle

SQL语句递归查询 sqlserver oracle
type: item
date: 2018-12-14T20:56:00+08:00
description: 对于一些自表关联的表，想要查出一条记录和它的所有下属子级，可以用下面的语句：sqlserver: with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) select * from my1; 查询出来删除可以这样写： with my1 as(select * from aaa where id = 1 union all select aaa.* from my1, aaa where my1.id = aaa.pid ) delete from aaa where id in (select id from my1); oracle:SELECT id FROM rightTree t START WITH t.id = #{ID} CONNECT BY PRIOR t.id = t.parent_id 查询出来删除可以这样写：delete from rightTree where id in( SELECT id FROM rightTree t START WITH t.id = #{ID} CONNECT BY PRIOR t.id = t.parent_id ) 参考：https://bbs.csdn.net/topics/350041264https://blog.csdn.net/qq_25391785/article/details/79136853