group case then

来源：互联网发布：淘宝数据魔方破解编辑：程序博客网时间：2024/06/11 18:31

create or replace view v$CheckData as select
emp_id,
count (case when input_type is not null and substr(number_to_bit(input_type),4,1)='1' then '1' else null end ) cq,
count (case when input_type is not null and substr(number_to_bit(input_type),2,1)='1' then '2' else null end ) cd,
count (case when input_type is not null and substr(number_to_bit(input_type),1,1)='1' then '3' else null end ) zt,
count (case when input_type is null and wtt<>'休班' then '4' else null end) kg
from WORKCHECKDATAS group by emp_id,plan_id;

select
emp_id,
sum(case when input_type is not null and substr(number_to_bit(input_type),4,1)='1' then 1 else null end) cq,
sum(case when input_type is not null and substr(number_to_bit(input_type),2,1)='1' then 1 else null end) cd,
sum(case when input_type is not null and substr(number_to_bit(input_type),1,1)='1' then 1 else null end) zt,
sum(case when input_type is null and wtt<>'休班' then 1 else null end) kg
from WORKCHECKDATAS where plan_id='HDR2014080001' group by emp_id;

create or replace view WORKCHECKDATAS as select wcd.input_type,ec.emp_id,plan_id ,ec.wtt from work_check_data wcd right
join(select ewpd.id,ewpd.emp_id, work_day, work_time_type, plan_id,card_code,wtt.name wtt from
emp_work_plan_detail ewpd left join work_time_type wtt on ewpd.WORK_TIME_TYPE=wtt.id
left join check_card cc ON ewpd.emp_id= cc.emp_id)ec
on wcd.card_code=ec.card_code and to_char(ec.work_day,'yyyyMMdd')=to_char(wcd.emp_input_time,'yyyyMMdd')
and wcd.input_type<>0

0 0