我看到有人发了这段sql ,但是又看不懂with mm as (select to_date(&mon||'01','YYYYMMDD')+ rownum-1 mday from dual connect by rownum <=31), yuangong as (select '员工1' yg from dual union all select '员工2' yg from dual union all select '员工3' yg from dual ), kaoqing as (select '员工1' yg,to_date('201001010812', 'YYYYMMDDHH24MISS') tt from dual union all select '员工1' yg,to_date('201001011612', 'YYYYMMDDHH24MISS') tt from dual union all select '员工2' yg,to_date('201001020712', 'YYYYMMDDHH24MISS') tt from dual ), my as ( select * from mm,yuangong where to_char(mday,'YYYYMM')= &mon), kqs as (select yg,to_date(to_char(tt,'YYYYMMDD'),'YYYYMMDD')mday,min(tt) mintt,max(tt)maxtt from kaoqing group by yg,to_char(tt,'YYYYMMDD')) select my.mday,my.yg, mintt,maxtt, case when mintt is null then '未打卡' when mintt >my.mday+8/24 and maxtt <my.mday+17/24 then '迟到早退' when mintt >my.mday+8/24 then '迟到' when maxtt <my.mday+17/24 then '早退' else null end from my,kqs where kqs.mday(+)=my.mday and kqs.yg(+)=my.yg
mm as (select to_date(&mon||'01','YYYYMMDD')+ rownum-1 mday from dual connect by rownum <=31),
yuangong as (select '员工1' yg from dual
union all
select '员工2' yg from dual
union all
select '员工3' yg from dual
),
kaoqing as (select '员工1' yg,to_date('201001010812', 'YYYYMMDDHH24MISS') tt from dual
union all
select '员工1' yg,to_date('201001011612', 'YYYYMMDDHH24MISS') tt from dual
union all
select '员工2' yg,to_date('201001020712', 'YYYYMMDDHH24MISS') tt from dual ),
my as ( select * from mm,yuangong where to_char(mday,'YYYYMM')= &mon),
kqs as (select yg,to_date(to_char(tt,'YYYYMMDD'),'YYYYMMDD')mday,min(tt) mintt,max(tt)maxtt from kaoqing group by yg,to_char(tt,'YYYYMMDD'))
select my.mday,my.yg, mintt,maxtt,
case when mintt is null then '未打卡'
when mintt >my.mday+8/24 and maxtt <my.mday+17/24 then '迟到早退'
when mintt >my.mday+8/24 then '迟到'
when maxtt <my.mday+17/24 then '早退'
else null
end
from my,kqs
where kqs.mday(+)=my.mday
and kqs.yg(+)=my.yg