With as 语句的用法

可以定义一个子查询字句

如：

with

t

as

(select *　ｆｒｏｍ　ｕｓｅｒ)

ｓｅｌｅｃｔ　＊ from t;

在工作中遇到了一个场景，需要查询该机构以及下辖机构id,如果使用with as 语句可以 直接写成

with

T(branch_id,branch_name,seq)

as(

select a.brach_id,a.branch_name,0 as seq from branch a where a.branch_id = '90000' and a.delete_state =1

union all

select b.branch_id ,b.branch_name seq+1 from branch b ,T t where b.delete_state=1 and b.parent_branch = t.brach_id

)

select branch_id from t;

或者为了控制查询的递归次数

select branch_id from t where seq <=2;