Oracle 的分析函数你知道吗?
解决方案 »
- oracle展现明细加小计合计
- 查询出人数最多的那个部门的部门编号和部门名称
- 用户不存在?
- 求助:数据库ORA-12514:TNS:listener does not...错误
- for 循环中取模的多进程执行程序块
- oracle 无故多了几个表,且有些type 无法删除 ,注:不久前电脑停电一次,会不会有什么关系
- 新手问几个问题
- 在oracle 10g 中修改了SYSMAN用户的密码后, 莫名被锁定!状态为locked(timed),如何解锁?
- 如何确定数据库该采用何种字符集:中文还是英文? 用户要求用英文,我有点想不通,不知道用户是怎么考虑的?
- oracle数据导入导出
- 一行的统计
- 各位ORACLE高手,请看看这奇怪的问题是何原因!
FROM (SELECT t5.ID, t1.num + t2.num + t3.num + t4.num + t5.num ttl
FROM mytable t1,
mytable t2,
mytable t3,
mytable t4,
mytable t5
WHERE t1.ID = t2.ID + 1
AND t1.ID = t3.ID + 2
AND t1.ID = t4.ID + 3
AND t1.ID = t5.ID + 4
ORDER BY ttl)
WHERE ROWNUM = 1;
from ( select a.time starttime, max(b.time) endtime, sum(b.num) total
from mytable a, mytable b
where b.time>=a.time and b.time<a.time+1
group by a.time
order by 3 desc)
where rownum=1;
select id_start, id_end from
(select id,num,
sum(num) over (order by id rows between 4 preceding and current row) sm,
first_value(id) over (order by id rows between 4 preceding and current row) id_start,
last_value(id) over (order by id rows between 4 preceding and current row) id_end
from t order by sm desc) where rownum=1) b
where a.id between b.id_start and id_end
select id_start, id_end from
(select id,num,
sum(num) over
(order by time range numToDSInterval(24,'HOUR') preceding) sum_num,
first_value(id) over
(order by time range numToDSInterval(48,'HOUR') preceding) id_start,
last_value(id) over
(order by itme range numToDSInterval(48,'HOUR') preceding) id_end
from t order by sum_num desc) where rownum=1) b
where a.id between b.id_start and id_end