递归查询

递归查询详解

递归查询经常用在树结构的表，带有 PID 的表。

Create table Depository
(
  ID varchar(50) not null primary key,
  Name varchar(50) not null,
  PID varchar(50) null
)

insert into Depository(ID,Name,PID) 
select 'A','A仓库',null 
union all
select 'A-1','A-1仓库','A' 
union all
select 'A-2','A-2仓库','A' 
union all
select 'A-1-1','A-1-1仓库','A-1' 
union all
select 'B','B仓库',null 

select * from DepositoryCode language: JavaScript (javascript)

使用 WITH AS 实现递归查询：

with tree as
(
  select * from Depository where ID = 'A'
  union all
  select d.* from Depository d,tree t where d.PID = t.ID
)
select * from treeCode language: JavaScript (javascript)