WITH RPL (FId,Fname,Forder) AS
(
SELECT ment.deptno,ment.deptname,ment.orderno
FROM JTERP..fg_department ment
WHERE ment.deptno in (
select mdept_mid from dbo.pl_managedept where mdept_usernum='' and mdept_usercomid=''
) and ment.ocode=''
UNION ALL
select part.deptno,part.deptname,part.orderno from
RPL as PARENT,JTERP..fg_department as part
where PARENT.FId=part.dept_fa and part.ocode=''
)
--SELECT FId,Fname,Forder FROM RPL;
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 ))+'"},'
from JTERP..secuser as se inner join JTERP..fg_user_actor as fg
on se.logid=fg.logid
inner join JTERP..fg_department as dept
on fg.deptno=dept.deptno and fg.ocode=dept.ocode where isnull(lg_sign,1)<>0 and fg.deptno in(
SELECT DISTINCT FId
FROM RPL
) order by se.logid asc
for xml path('')),'⒈','');