--由父项递归下级

with cte(refid,pid,zname,code)

as

(--父项

select refid,pid,zname,code from zuzhi where pid =

union all

--递归结果集中的下级

select t.refid,t.pid,t.zname,t.code from zuzhi as t

inner join cte as c on t.pid = c.refid

)

select refid,pid,zname,code from cte