区间包括开始时间,不包括结束时间,类型是[begintime,endtime)
是比较难,我用
select * from
(select id,times,lead(times) over(order by times) nexttime from
(
select rownum id,to_date('2009-06-01','yyyy-mm-dd')+(rownum-1)*30/(24*60) times
from all_objects
where rownum<=49)
)
where nexttime is not null可以得出时间的区间,但是这个查询怎么和t表关联,实在想不出来是做题的题目要求一个查询的,呵呵
是比较难,我用
select * from
(select id,times,lead(times) over(order by times) nexttime from
(
select rownum id,to_date('2009-06-01','yyyy-mm-dd')+(rownum-1)*30/(24*60) times
from all_objects
where rownum<=49)
)
where nexttime is not null可以得出时间的区间,但是这个查询怎么和t表关联,实在想不出来是做题的题目要求一个查询的,呵呵
select a1.dateExtend, count(*) num
from(
select
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30 then '00:00~00:30'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*2 then '00:30~01:00'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*3 then '01:00~01:30'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*4 then '01:30~02:00'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*5 then '02:00~02:30'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*6 then '02:30~03:00'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*7 then '03:00~03:30'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*8 then '03:30~04:00'
--这里省略N个case when 语句,反正都是copy的,都是以30分钟来区分的
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*46 then '22:30~23:00'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*47 then '23:00~23:30'
case when (to_char(begintime,'hh24')*60+to_char(sysdate,'mi'))<30*48 then '23:30~24:00'
else '' end as dateExtend
from t where ceil(to_date('2009-06-01' , 'yyyy-mm-dd')-begintime)=0 --这里判断是2009-06-01这天的数据
)a1 group by a1.dateExtend
写了sql如下,在你的基础上写的,那个id可能是1,2,3,4,这样的了,代表第1个30分钟,第2个30分钟......,你看下效果如何?select a4.id, count(*)
from(
select a3.times, a3.nexttime, a3.id from T
join
(
select * from
(
select id,times,lead(times) over(order by times) nexttime from
(
select rownum id,to_date('2009-06-01','yyyy-mm-dd')+(rownum-1)*30/(24*60) times
from all_objects
where rownum <=49
)a1
)a2
where a2.nexttime is not null
)a3
on T.begintime between a3.times and a3.nexttime
)a4 group by a4.id
SELECT R, TIM, NVL(C, 0)
FROM (SELECT floor((begintime - trunc(begintime)) * 48) R, COUNT(1) c
FROM t
WHERE begintime BETWEEN to_date('2009-6-1', 'yyyy-mm-dd') --
AND to_date('2009-6-1', 'yyyy-mm-dd') + 1 - 1e-5
GROUP BY floor((begintime - trunc(begintime)) * 48))
RIGHT JOIN (SELECT ROWNUM - 1 R,
to_char(trunc(SYSDATE) + (ROWNUM - 1) / 48, 'HH24:MI"--"') ||
to_char(trunc(SYSDATE) + ROWNUM / 48, 'HH24:MI') TIM
FROM dual
CONNECT BY ROWNUM <= 48)
USING (R)
ORDER BY R
select to_char(tmp_time,'yyyymmddhh24mi'),
case when substr(to_char(tmp_time,'yyyymmddhh24mi'),11,2)>30
then substr(to_char(tmp_time,'yyyymmddhh24mi'),9,2)*2+1
else
substr(to_char(tmp_time,'yyyymmddhh24mi'),9,2)*2 end HH
from tmp where to_char(begintime,'yyyymmdd')='yyyymmdd' group by HH order by 1;