select

n.name,n.position,

case

  when

  (select count(*) from T_PUNISHMENT c where c.user_id = n.id) > 0

  then

  '有'

  else

  '无'

end PUNISHMENT,

case

  when

  (select count(*) from T_ASK_AND_CONVERSATION c where c.user_id = n.id) > 0

  then

  '有'

  else

  '无'

end CONVERSATION,

case

  when

  (select count(*) from T_REPORT_ON_WORK b where b.user_id = n.id) > 0

  then

  '有'

  else

  '无'

end REPORT_WORK,

case

  when

  (select count(*) from T_INTEGRITY_ASSESS_REPORT b where b.user_id = n.id) > 0

  then

  '有'

  else

  '无'

end REPORT_ASSESS

from tor_horizon_user_dept m,to_horizon_user n where m.dept_id = (

select dept_id from tor_horizon_user_position thup,to_horizon_position thp,TOR_HORIZON_USER_DEPT thud where

thup.user_id = 'HZc18c545ab19f94015ab1c564fa00ff' and thp.position_type_id ='HZc18ca15a83d637015a87d8c0550682'

and thup.position_id = thp.id and thup.user_id = thud.user_id) and m.user_id = n.id;

select s.*,

  case

  when

  (select 3+(to_char(trunc(sysdate,'yyyy'),'yyyy')-substr(work_begin_time, 0, 4)-1)*2 as total_vacation_day from t_work_duration u where u.card_no = s.card_no) > 7

  then

  7

  else

  (select 3+(to_char(trunc(sysdate,'yyyy'),'yyyy')-substr(work_begin_time, 0, 4)-1)*2 as total_vacation_day from t_work_duration u where u.card_no = s.card_no)

  end total_vacation_day

from

(

  select p.id,p.name,p.card_no,p.work_begin_time,p.unit_work_time,p.enjoy_summer_winter,p.vacation_begin_time,p.vacation_end_time,q.already_vacation_day,

  q.all_vacation_day from t_work_duration p left join

  (

    select m.id,m.name,m.card_no,m.work_begin_time,m.unit_work_time,m.enjoy_summer_winter,m.vacation_begin_time,

    m.vacation_end_time,n.already_vacation_day,t.all_vacation_day from t_work_duration m,

    (

      select card_no,sum(VACATE_DAY) as already_vacation_day

      from T_VACATION,(select trunc(sysdate,'y') as first_day FROM DUAL)m,(select last_day(add_months(trunc(SYSDATE,'y'),11)) as last_day from dual)n

      where to_date(vacate_begin_time,'yyyy-MM-dd') >= m.first_day and to_date(vacate_begin_time,'yyyy-MM-dd') <= n.last_day

      group by card_no

    )n,

    (

      select card_no,sum(VACATE_DAY) as all_vacation_day from T_VACATION

      group by card_no

    )t

    where m.card_no = n.card_no and m.card_no = t.card_no

  )q on p.card_no = q.card_no

)s;