SQL语句递归查询 sqlserver oracle

对于一些自表关联的表，想要查出一条记录和它的所有下属子级，可以用下面的语句：
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/350041264
https://blog.csdn.net/qq_25391785/article/details/79136853