select sysdate-5*m b,sysdate-5*(m-1) e,(select count(1) from xxx where date>b and date<e) from (select rownum m from xxx where rownum<10)
上面的错了select b,e,(select count(1) from xxx t where t.date>b and t.date<e) from (select sysdate-5*m b,sysdate-5*(m-1) e from (select rownum m from xxx where rownum<10))
搞定了!其中的'7'就是小时间段,between的两个日期就是时间区间,这里的sum()函数中的字段的数据都是个数,所以不用count()也可以,否则稍作修改用count(1)统计一下就行了。 ---------------- SELECT floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7,sum(c.recorders),sum(d.users_order),sum(d.users_cancel),sum(d.netadd) FROM v_stat_orders_current c,v_stat_orders_dynamic d WHERE to_date(c.report_date,'yyyymmdd') BETWEEN to_date('20031202','yyyymmdd') AND to_date('20031202','yyyymmdd') GROUP BY floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7 ORDER BY floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7
select decode('abc','a',1,
'b',2,
'abc',3,
-1)"Decode 1"
from dual
Decode 1
-----------
3
----------------
SELECT floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7,sum(c.recorders),sum(d.users_order),sum(d.users_cancel),sum(d.netadd)
FROM v_stat_orders_current c,v_stat_orders_dynamic d
WHERE to_date(c.report_date,'yyyymmdd')
BETWEEN to_date('20031202','yyyymmdd') AND to_date('20031202','yyyymmdd')
GROUP BY floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7
ORDER BY floor((to_date(c.report_date,'yyyymmdd')-to_date('20031202','yyyymmdd'))/7)*7