SQLÓï¾ä

Ò»table,¿ªÊ¼ºÍ½áÊøʱ¼ä
start_time  ||  end_time 
  8:30      ||  12:00
12:30      ||  17:00
17:30      ||  22:30

12£º00--12£º30£¬17£º00--17£º30 ÕâÊÇÐÝϢʱ¼ä£»

ÁíÍâÒ»¸ö±í¼Ç¼ʱ¼ä
curtime
8£º45
8£º55
11£º09
14£º00
16£º00
18£º00
18£º50
19£º00

Ïë¸ù¾ÝµÚÒ»¸ö±íÇó³ö±í¶þµÄʵ¼Ê¹¤×÷ʱ¼ä£¬
±ÈÈ磬  µ½ÁË 14£º00£¬Êµ¼Ê¹¤×÷ʱ¼äΪ14£º00 - 8£º30 - 30'(ÐÝϢʱ¼ä)=3H;
            19:00,ʵ¼Ê¹¤×÷ʱ¼äΪ19£º00-8£º30-30'-30¡¯=10.5H;

---

SQL code:
select b.curtime,
nvl(sum(a1.end_time-a1.start_time),0)+nvl(max(b.curtime-a2.start_time),0) total
from a a1,a a2,b
where b.curtime>a1.end_time(+)
and b.curtime>a2.start_time(+) and
b.curtime<=a2.end_time(+)
group by b.curtime

ʱ¼äÈç¹û²»ÊÇdateÐ͵Ä×Ô¼ºÒª×ªÒ»ÏÂ

---

SQL code:
SELECT (TO_DATE(curtime,'hh24:mi') - TO_DATE((SELECT TO_CHAR(MIN(TO_DATE(start_time,'hh24:mi')),'hh24:mi') from tb1),'hh24:mi'))*24 -
(CASE WHEN TO_DATE(curtime,'hh24:mi') > TO_DATE((SELECT TO_CHAR(MAX(TO_DATE(end_time,'hh24:mi'