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

时间:2022-08-23 13:24:01

说明:将多行(包含父子关系)树状查询结果符合条件的父子关系记录按照符号进行等级层次拼接组成每行结果;其中包含两种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;