IT技术互动交流平台

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

来源:IT165收集  发布日期:2016-05-06 21:34:33

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

 

Tag标签: 递归   节点   父子  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规