|
查询组织机构
select a.dept_id, a.部门名称, a.upper_id, a.sort_order, b.状态,b.id
from (select DISTINCT dept_id,
dept_name 部门名称,
upper_id,
dept_type,
sort_order
from sa_dept_dict
where dept_type = 2
start with dept_id in
(select dept_id from sa_dept_dict where dept_type = 2)
connect by prior dept_id = upper_id) a,
(select 填表人部门ID,id,
case
when sum(decode(状态, '已完成', 1, 0)) > 0 then
'已完成'
else
'还未通过'
end 状态
from xy_职工年休假
where 年度 = [:年度]
and 状态 = '已完成'
group by 填表人部门ID,id) b
where a.dept_id = b.填表人部门ID(+)
order by a.sort_order
效果图:
|
|