select * from table1 t where to_char(t.start_time, 'hh24:mi:ss') between to_char(to_date('09:30:00','hh24:mi:ss'), 'hh24:mi:ss') and to_char(to_date('10:30:00','hh24:mi:ss'), 'hh24:mi:ss')
请参考(根据需求不同改group by),刚回到另个一问题写的: WITH TAB AS (SELECT TO_DATE('2011-08-03 12:23:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE, TO_DATE('2011-08-03 12:25:45 ', 'yyyy-mm-dd hh24:mi:ss') AS ENDDATE, 1 AS FLAG FROM DUAL
UNION SELECT TO_DATE('2011-08-03 12:29:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE, -- 在11:30-12:30有一条通话记录,在12:30-13:30也有通话记录 TO_DATE('2011-08-03 12:33:45 ', 'yyyy-mm-dd hh24:mi:ss'), 1 AS FLAG FROM DUAL UNION SELECT TO_DATE('2011-08-03 12:50:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE, TO_DATE('2011-08-03 12:50:45 ', 'yyyy-mm-dd hh24:mi:ss'), 0 AS FLAG FROM DUAL UNION SELECT TO_DATE('2011-08-03 09:50:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE, TO_DATE('2011-08-03 10:50:45 ', 'yyyy-mm-dd hh24:mi:ss'), 0 AS FLAG FROM DUAL) SELECT TO_CHAR(TDATE.BENGDATE, 'hh24:mi') || '-' || TO_CHAR(TDATE.ENDDATE, 'hh24:mi') TIME, SUM(FLAG) AS 成功通话条数, SUM(DECODE(FLAG, 0, 1, 0)) AS 未接通条数 FROM TAB, (SELECT (TO_DATE('2011-08-03 09:30:00', 'yyyy-mm-dd hh24:mi:ss') + -- '2011-08-03 09:30:00'是要查询的开始时间 (ROWNUM - 1) / 24) BENGDATE, (TO_DATE('2011-08-03 09:30:00', 'yyyy-mm-dd hh24:mi:ss') + (ROWNUM) / 24) ENDDATE FROM DUAL CONNECT BY ROWNUM < 17 -- 需要查询的时间的小时数 ) TDATE WHERE TAB.STARTDATE BETWEEN TDATE.BENGDATE AND TDATE.ENDDATE OR TAB.ENDDATE BETWEEN TDATE.BENGDATE AND TDATE.ENDDATE GROUP BY TO_CHAR(TDATE.BENGDATE, 'hh24:mi') || '-' || TO_CHAR(TDATE.ENDDATE, 'hh24:mi') ORDER BY TIME
select * from table a where to_char(a.date,'HH:mi') between '09:30' and '10:30' or to_char(a.date,'HH:mi') between '10:30' and '11:30' 这个是最简单的办法了。。
select * from table a where to_char(a.date,'HH:mi:ss') between '09:30:00' and '10:30:00' or to_char(a.date,'HH:mi:ss') between '10:30:00' and '11:30:00'
from table1 t
where to_char(t.start_time, 'hh24:mi:ss') between
to_char(to_date('09:30:00','hh24:mi:ss'), 'hh24:mi:ss') and
to_char(to_date('10:30:00','hh24:mi:ss'), 'hh24:mi:ss')
WITH TAB AS
(SELECT TO_DATE('2011-08-03 12:23:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE,
TO_DATE('2011-08-03 12:25:45 ', 'yyyy-mm-dd hh24:mi:ss') AS ENDDATE,
1 AS FLAG
FROM DUAL
UNION
SELECT TO_DATE('2011-08-03 12:29:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE, -- 在11:30-12:30有一条通话记录,在12:30-13:30也有通话记录
TO_DATE('2011-08-03 12:33:45 ', 'yyyy-mm-dd hh24:mi:ss'),
1 AS FLAG
FROM DUAL
UNION
SELECT TO_DATE('2011-08-03 12:50:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE,
TO_DATE('2011-08-03 12:50:45 ', 'yyyy-mm-dd hh24:mi:ss'),
0 AS FLAG
FROM DUAL
UNION
SELECT TO_DATE('2011-08-03 09:50:32', 'yyyy-mm-dd hh24:mi:ss') STARTDATE,
TO_DATE('2011-08-03 10:50:45 ', 'yyyy-mm-dd hh24:mi:ss'),
0 AS FLAG
FROM DUAL)
SELECT TO_CHAR(TDATE.BENGDATE, 'hh24:mi') || '-' || TO_CHAR(TDATE.ENDDATE, 'hh24:mi') TIME,
SUM(FLAG) AS 成功通话条数,
SUM(DECODE(FLAG, 0, 1, 0)) AS 未接通条数
FROM TAB,
(SELECT (TO_DATE('2011-08-03 09:30:00', 'yyyy-mm-dd hh24:mi:ss') + -- '2011-08-03 09:30:00'是要查询的开始时间
(ROWNUM - 1) / 24) BENGDATE,
(TO_DATE('2011-08-03 09:30:00', 'yyyy-mm-dd hh24:mi:ss') +
(ROWNUM) / 24) ENDDATE
FROM DUAL
CONNECT BY ROWNUM < 17 -- 需要查询的时间的小时数
) TDATE
WHERE TAB.STARTDATE BETWEEN TDATE.BENGDATE AND TDATE.ENDDATE
OR TAB.ENDDATE BETWEEN TDATE.BENGDATE AND TDATE.ENDDATE
GROUP BY TO_CHAR(TDATE.BENGDATE, 'hh24:mi') || '-' ||
TO_CHAR(TDATE.ENDDATE, 'hh24:mi')
ORDER BY TIME
这个是最简单的办法了。。