count 应用

select distinct (category) categorynum, o.cnname categoryname
  from ycoms_askforleave s, s_dic o
 where s.approve_status = '997'
   and s.actorno = '004685'
   and s.category = o.enname
   and o.opttype = 'AFL_LEAVESTATE'
   and s.askforleaveid in (select oo.askforleaveid
                             from ycoms_afl_detailed oo
                            where oo.cancelleaveid is null
                            group by oo.askforleaveid)
      --and to_char(to_date(s.edittime,'yyy-mm-dd'), 'yyyy-mm-dd')
   and substr(ltrim(s.creattime), 0, 10) >
      --to_char(to_date(
       (select t.attendendd
          from (select max(attendendd) as attendendd
                  from ycoms_attend_date_hissta
                 where state in ('2', '1')
                 order by attendendd asc) t) --,'yyyy-mm-dd'),'yyyy-mm-dd')

      
   and s.category = (case
         when s.category = '01' and
              '02' in (select ta.category
                         from ycoms_askforleave ta
                        where ta.approve_status = '997'
                          and ta.actorno = '004685') and
              (select count(x.cancelleaveid)
                 from ycoms_afl_detailed x
                 left join ycoms_askforleave cc
                   on x.askforleaveid = cc.askforleaveid
                where cc.category = '02'
                  and cc.approve_status = '997'
                  and cc.actorno = '004685') in
              (select count(*)
                 from ycoms_afl_detailed x
                 left join ycoms_askforleave cc
                   on x.askforleaveid = cc.askforleaveid
                where cc.approve_status = '997'
                  and cc.actorno = '004685'
                  and cc.category = '02') then
          '01'
         when s.category = '01' and
              '02' in (select ta.category
                         from ycoms_askforleave ta
                        where ta.approve_status = '997'
                          and ta.actorno = '004685') and
              (select count(x.cancelleaveid)
                 from ycoms_afl_detailed x
                 left join ycoms_askforleave cc
                   on x.askforleaveid = cc.askforleaveid
                where cc.category = '02'
                  and cc.approve_status = '997'
                  and cc.actorno = '004685') not in
              (select count(*)
                 from ycoms_afl_detailed x
                 left join ycoms_askforleave cc
                   on x.askforleaveid = cc.askforleaveid
                where cc.approve_status = '997'
                  and cc.actorno = '004685'
                  and cc.category = '02') then
          ''
         else
          s.category
       end)