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('')),'⒈','');
