Oracle与SQL-Server数据库SQL"树查询"对比[按照父子节点关系递归拼接]

说明：将多行(包含父子关系)树状查询结果符合条件的父子关系记录按照符号进行等级层次拼接组成每行结果；其中包含两种SQLServer和Oracle数据库的查询方式，仅供参考，目前正在学习中，当中有语法不规范处请勿对号入座。

1. 【SQLServer】"树查询"拼接语句如下：

With SubQuery (upmaterialnumber,qty,subPartNo,SumDrawingNumber) AS  (Select        R.upmaterialnumber,         R.qty, R.materialnumber as subPartNo,C.SumDrawingNumber as SumDrawingNumber    From ERP_D_MeterialRelation R,ERP_D_BillOfMaterial C    Where R.materialnumber = '6A100045'    And R.materialnumber = C.number Union All    Select         A1.upmaterialnumber,A1.qty,A1.subPartNo,B1.SumDrawingNumber   From ERP_D_BillOfMaterial B1,       (Select      A.upmaterialnumber,      A.qty,      A.materialnumber as subPartNo        From ERP_D_MeterialRelation A Left Join SubQuery B ON A.upmaterialnumber = B.subPartNo) A1   Where A1.subPartNo = B1.Number)Select * From SubQuery

2. 【Oracle】"树查询"拼接语句如下：

Select        c.roleName as roleName,        c.menuName as menuName,        convert(varchar(5000),c.funName) as funName  From (        Select      roleName,      (Select name as menuName From t_scl_func where code = supCode) as menuName,     funName =  (stuff((                 Select ','  b.funname  From (        Select      r.name as rolename,     m.name as funname,     m.code as code,     m.parent as supCode        From      t_scl_role r,     t_scl_role_func f,     t_scl_func m         Where r.id = f.role_id         And f.func_code = m.code ) b  Where b.rolename = a.rolename   And b.supCode = a.supCode for xml path('')),1,1,'')) From (      Select     r.name as rolename,    m.name as funname,    m.code as code,    m.parent as supCode       From     t_scl_role r,    t_scl_role_func f,    t_scl_func m        Where r.id = f.role_id        And f.func_code = m.code ) a Group by rolename,supCode) c  Where c.funname is Not Null Order by c.roleName;