select
70000 as req_max,
3000 as req_min,
t.dayName as hour,
n.reqCnt as req_cnt
from (select a.day_id || lpad(b.minute_code, 3, '0') as dayId,
a.day_id || b.minute_name as dayName
from dim_time_day a, dim_time_minute b
where to_number(a.day_id || lpad(b.minute_code, 3, '0')) between
(select to_number(to_char(sysdate-1, 'yyyymmdd') || lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5) + 1), 3 ,'0'))
from dual)
and
(select to_number(to_char(sysdate, 'yyyymmdd') ||
lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5)), 3 ,'0')) from dual)) t
left join (select t.req_cnt as reqCnt, t.partition_date as dayId
from fct_portal_collect_min t
where t.partition_date between
(select to_number(to_char(sysdate-1, 'yyyymmdd') || lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5) + 1), 3 ,'0'))
from dual)
and
(select to_number(to_char(sysdate, 'yyyymmdd') ||
lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5)), 3 ,'0')) from dual) order by t.partition_date ) n
on t.dayId = n.dayId上面sql语句中between部分得数据用了两次,请问是否可以优化下呢 sql优化
70000 as req_max,
3000 as req_min,
t.dayName as hour,
n.reqCnt as req_cnt
from (select a.day_id || lpad(b.minute_code, 3, '0') as dayId,
a.day_id || b.minute_name as dayName
from dim_time_day a, dim_time_minute b
where to_number(a.day_id || lpad(b.minute_code, 3, '0')) between
(select to_number(to_char(sysdate-1, 'yyyymmdd') || lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5) + 1), 3 ,'0'))
from dual)
and
(select to_number(to_char(sysdate, 'yyyymmdd') ||
lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5)), 3 ,'0')) from dual)) t
left join (select t.req_cnt as reqCnt, t.partition_date as dayId
from fct_portal_collect_min t
where t.partition_date between
(select to_number(to_char(sysdate-1, 'yyyymmdd') || lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5) + 1), 3 ,'0'))
from dual)
and
(select to_number(to_char(sysdate, 'yyyymmdd') ||
lpad(to_char(to_number(to_char(sysdate, 'hh24'))*12
+ floor(to_number(to_char(sysdate, 'mi'))/5)), 3 ,'0')) from dual) order by t.partition_date ) n
on t.dayId = n.dayId上面sql语句中between部分得数据用了两次,请问是否可以优化下呢 sql优化
解决方案 »
- 请高手帮忙写个oracle命令?跪求!
- oracle日期转换问题,请高手指教.谢谢
- 平均薪水最高的部门名称 问题》?》》
- Oracle 10g Express Editon如何增加最大会话数
- 各位老大,哪里有oracle 9i for win200/winnt下载?
- 在触发器中要从被触发表相关的视图中取数据出错的问题
- 紧急!如何在WINDOWS2000上安装ORACLE73
- 请问哪里有“Oracle9i中文版”下载?
- 简单问题,如何将数据库文件重定向?
- 外联结的SQL:3个外联结结果正常,4个外联结怎么就不行了?
- 急啊,Oracle监听程序启动不了
- resultset 读取并显示一行数据后,程序崩溃
select a.day_id || lpad(b.minute_code, 3, '0') as dayId,
a.day_id || b.minute_name as dayName
from dim_time_day a, dim_time_minute b
where to_number(a.day_id || lpad(b.minute_code, 3, '0'))