oracle sql第几周,用SQL语句得到指定日期所在的周是本年度第几周

昨天，客户提了一个需求，需求中有要用到求指定日期所在的周是本年度的第几周

看到这个问题，我很自然的想到了to_char(d,'ww')这个用法：

SQL> select date'2010-01-1' + rownum - 1 r,

2         to_char(date'2010-01-1' + rownum - 1,'d')-1 wn,

3         to_number(to_char(date'2010-01-1' + rownum - 1,'ww')) nthweek

4    from dual

5  connect by rownum <= date'2010-01-27' - date'2010-01-1' + 1

6  /

R                   WN    NTHWEEK

----------- ---------- ----------

2010-1-1             5          12010-1-2             6          12010-1-3             0          1

2010-1-4             1          12010-1-5             2          12010-1-6             3          12010-1-7             4          12010-1-8             5          22010-1-9             6          22010-1-10            0          2

2010-1-11            1          22010-1-12            2          22010-1-13            3          22010-1-14            4          22010-1-15            5          32010-1-16            6          32010-1-17            0          3

2010-1-18            1          32010-1-19            2          32010-1-20            3          32010-1-21            4          32010-1-22            5          42010-1-23            6          42010-1-24            0          4

2010-1-25            1          4

2010-1-26            2          4

2010-1-27            3          4

27 rows selected

从以上数据来看，第一组颜色是对应的真正的第一周，但这个函数却把第一个7天作为了第一周，使得实际的第一周跟得到的第一周差了4天的时间，这四天直接导致了后面数据的不准确。貌似网上也没有查到过直接可以解决这个问题的Oracle系统函数，没办法，只好自己写了个SQL：

SQL> with tmp as(select date'2010-01-27' d from dual)

2  select r, wn,

3         sum(decode(wn, 1, 1, 0))over(order by rownum) +

4         decode(to_char(trunc(d, 'y') - 1, 'd'),1,1,1) nthweek

5    from (select trunc(d, 'y') + rownum - 1 r, d,

6                 to_char(trunc(d, 'y') + rownum - 1, 'd') - 1 wn

7            from tmp

8          connect by rownum <= d - trunc(d, 'y') + 1)

9  /

R                   WN    NTHWEEK

----------- ---------- ----------

2010-1-1             5          12010-1-2             6          12010-1-3             0          1

2010-1-4             1          22010-1-5             2          22010-1-6             3          22010-1-7             4          22010-1-8             5          22010-1-9             6          22010-1-10            0          2

2010-1-11            1          32010-1-12            2          32010-1-13            3          32010-1-14            4          32010-1-15            5          32010-1-16            6          32010-1-17            0          3

2010-1-18            1          42010-1-19            2          42010-1-20            3          42010-1-21            4          42010-1-22            5          42010-1-23            6          42010-1-24            0          4

2010-1-25            1          5

2010-1-26            2          5

2010-1-27            3          5

27 rows selected

这次，显示的结果便是真正要求的第几周了。

下面的脚本是直接得到第几周的：

with tmp as(select date'2010-01-27' d from dual)

select sum(1)+decode(to_char(t.d,'d'),1,1,1) nthweek

from (select to_char(trunc(d, 'y') + rownum - 1, 'd') - 1 nd

from tmp

connect by rownum <= d - trunc(d, 'y') + 1), tmp t

where nd = 1

实现的原理其实很简单，即首先把当前所在日期的年的第一天取出，然后构造从第一天到改天的所有数据(最多也就366条)，并把这些天都转换成星期几(与实际星期几对应)，最后把每个星期一都取出来进行累加，考虑到年初第一天可能不是周一，需要特殊处理外，其他直接相加即可