sql with as union all

 1 WITH RPL (FId,Fname,Forder) AS         
 2 (         
 3 SELECT ment.deptno,ment.deptname,ment.orderno      
 4 FROM JTERP..fg_department ment 
 5 WHERE ment.deptno in (
 6 select mdept_mid from dbo.pl_managedept where mdept_usernum='0070' and mdept_usercomid='018'
 7 ) and ment.ocode='018'
 8 UNION ALL        
 9 select part.deptno,part.deptname,part.orderno from
10 RPL as  PARENT,JTERP..fg_department as part
11 where PARENT.FId=part.dept_fa and part.ocode='018'
12 )
13 --SELECT FId,Fname,Forder FROM RPL;
14 select  replace((select '{logid:"'+ se.logid+'" , u_name:"' +se.u_name+'" , deptname:"'+ dept.deptname+'" , num:"'+convert(varchar(10) ,row_number() over(order by se.logid asc ))+'"},' 
15 from JTERP..secuser as se inner join JTERP..fg_user_actor as fg
16 on se.logid=fg.logid
17 inner join JTERP..fg_department as dept
18 on fg.deptno=dept.deptno and fg.ocode=dept.ocode where    isnull(lg_sign,1)<>0 and fg.deptno in(
19 SELECT DISTINCT  FId      
20 FROM RPL 
21 )  order by se.logid asc
22 for xml path('')),'⒈','');

 

转载于:https://www.cnblogs.com/liudabao123/p/5729772.html