根据用户ID查询上级和上上级组织机构

select pol.*,
pss.sub_sys_name as sub_sys_name, --子系统名
pui.user_name as oper_user_name, --用于名
pui.real_name as oper_real_name, --用户真名
psf.fun_name as fun_name, --功能点名
psm.mod_name as mod_name, --模块名
poi.org_name as org_name, --组织机构名
office_list.org_name as office_name, --派出所名
station_list.org_name as station_name --分局名
from PT_OPER_LOG pol
left join Pt_Sub_System pss on pol.sub_sys_id = pss.id
left join Pt_User_Info pui on pol.oper_id = pui.id
left join PT_SYS_FUN psf on pol.oper_type = psf.id
and pol.sub_sys_id = psf.sub_sys_id
left join PT_SYS_MODULE psm on pol.mod_id = psm.mod_id
left join pt_org_info poi on pui.org_id = poi.id
left join (select *
from (select *
from pt_org_info poi
start with id = poi.id
connect by id = prior par_id)) office_list on office_list.id =
poi.par_id
left join (select *
from (select *
from pt_org_info poi
start with id = poi.id
connect by id = prior par_id)) station_list on station_list.id =
office_list.par_id

 

转载于:https://www.cnblogs.com/chuang8/p/6654458.html