ORACLE 根据用户ID取出组织下所有用户的信息

--zl_t_project项目信息表
--bos_t_user 用户信息表
--BOS_T_ORG组织机构信息表
--9000000000001用户ID
select distinct zp.project_id,
                zp.project_no,
                zp.project_name,
                zp.project_type,
                zp.business_type,
                to_char(zp.start_date, 'yyyy-mm-dd') as sd,
                zp.entrust_unit as upi,
                zp.architect_unit as spi,
                zp.construction_unit as usp,
                zp.audit_status,
                zp.operating_state,
                tu.user_name,
                zp.create_date
  from zl_t_project zp
  left join zl_t_project_person pp
    on zp.project_id = pp.project_id
  left join bos_t_user tu
    on tu.user_id = zp.pro_liability_preson
 where 1 = 1
   and (pp.user_id in (SELECT distinct bu.user_id
  FROM bos_t_user bu
 WHERE bu.org_id in
       (select bo.org_id
          from BOS_T_ORG bo
        connect by prior bo.org_id = bo.parent_id
         start with bo.org_id = (select distinct bt.org_id
  from BOS_T_ORG bt left join bos_t_user tu on bt.org_id=tu.org_id
 where  bt.is_valid = 1 and tu.user_id=9000000000001))) )
 order by zp.create_date desc