日期:2014-05-16 浏览次数:21038 次
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') ) );
------解决方案--------------------
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' );