3,494
社区成员




select sum(8) H
from (select to_date('2009-08-01','yyyy-mm-dd')-1 + rownum days from dual
connect by rownum <=to_date('2009-08-17','yyyy-mm-dd')-to_date('2009-08-01','yyyy-mm-dd')+1
) t1
where exists (select 1 from tablename t
where ((t.begdate between to_date(to_char(t1.days,'yyyy-mm-dd')||' 08:00:01','yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(t1.days,'yyyy-mm-dd')||' 22:00:00','yyyy-mm-dd hh24:mi:ss')
)
OR (t.begdate between to_date(to_char(t1.days,'yyyy-mm-dd')||' 22:00:01','yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(t1.days+1,'yyyy-mm-dd')||' 06:00:00','yyyy-mm-dd hh24:mi:ss')
))
AND t.worker = 'worker1'
);
select sum(8) H
from (select to_date('2009-08-01','yyyy-mm-dd')-1 + rownum days from dual
connect by rownum <=to_date('2009-08-17','yyyy-mm-dd')-to_date('2009-08-01','yyyy-mm-dd')+1
) t1
where exists (select 1 from tablename t
where (t.begdate between to_date(to_char(t1.days,'yyyy-mm-dd')||' 08:00:01','yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(t1.days,'yyyy-mm-dd')||' 22:00:00','yyyy-mm-dd hh24:mi:ss')
)
OR (t.begdate between to_date(to_char(t1.days,'yyyy-mm-dd')||' 22:00:01','yyyy-mm-dd hh24:mi:ss')
and to_date(to_char(t1.days+1,'yyyy-mm-dd')||' 06:00:00','yyyy-mm-dd hh24:mi:ss')
)
);