今天很高兴,又学到了定义时间段的SQL语句.感觉这个语句在业务上很有用处,所以写下来以备后用.
以 SCOTT.EMP 表为例, 我们要看连续20天内到职人数最多的日期范围是什么, 如下:
select hiredate, hiredate + 20,cnt
from (select hiredate,
count(0) over(order by hiredate range between current row and 20 following) cnt
from emp
order by cnt desc, hiredate)
where rownum = 1;
HIREDATE HIREDATE+20 CNT
----------- ----------- ----------
1981-11-17 1981-12-7 3
这是nyfor给你例子.
下面我自己实际业务做个测试,求2008-10-1以来哪个一个星期内开的销售单最多.
SELECT ORDDT,ORDDT+7,CNT FROM
(SELECT distinct ORDDT,COUNT(ORDSEQ) OVER (ORDER BY ORDDT range between current row and 7 following ) cnt from (select ordseq,trunc(orddt,'dd') orddt
from ord
where orddt>trunc(ADD_MONTHS(sysdate,-1),'MM') and rdty='S')
order by cnt desc,orddt)
where rownum=1
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13387766/viewspace-483813/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13387766/viewspace-483813/