select A.User_id, A.OA, B.tongyong
from (select usrid,count(1) OA from oa_doc_ff f
where NOT exists (SELECT USER_ID FROM TBLUSER g WHERE POSITION=57 and f.USRID=g.USER_ID)
and isend =0 and nvl(EDate,Sysdate)-CDate >10
group by usrid) A,
(select checkuser,count(1) tongyong from oa_flow_child c
where checkuser in (select user_id from tbluser)
and flowagree is null and sysdate-recdate>1 group by checkuser) B
where A.UserId = B.CheckUser(+)
from (select usrid,count(1) OA from oa_doc_ff f
where NOT exists (SELECT USER_ID FROM TBLUSER g WHERE POSITION=57 and f.USRID=g.USER_ID)
and isend =0 and nvl(EDate,Sysdate)-CDate >10
group by usrid) A,
(select checkuser,count(1) tongyong from oa_flow_child c
where checkuser in (select user_id from tbluser)
and flowagree is null and sysdate-recdate>1 group by checkuser) B
where A.UserId = B.CheckUser(+)
from TBLUSER a
left join
(select checkuser,count(*) tongyong from oa_flow_child c
where checkuser in (select user_id from tbluser) and flowagree is null and
sysdate-recdate>1 group by checkuser
)b on a.user_id = b.checkuser
left join
(select usrid,count(1) OA from oa_doc_ff f
where NOT exists
(SELECT USER_ID FROM TBLUSER g WHERE POSITION=57 and f.USRID=g.USER_ID)
and isend =0 and nvl(EDate,Sysdate)-CDate >10
group by usrid
)c on a.user_id = c.usrid
select A.*,B.OA from A,B where A.fsm=B.fsm(+)
nvl(a.oa,0) oa,nvl(b.tongyong,0) tongyong
from
(select usrid,count(1) OA from oa_doc_ff f
where NOT exists
(SELECT USER_ID FROM TBLUSER g WHERE POSITION=57 and f.USRID=g.USER_ID)
and isend =0 and nvl(EDate,Sysdate)-CDate >10
group by usrid) a
full outer join
(select checkuser,count(*) tongyong from oa_flow_child c
where checkuser in (select user_id from tbluser) and flowagree is null and sysdate-recdate>1 group by checkuser) b
on a.usrid = b.checkuser