用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          1
2010-1-2             6          1
2010-1-3             0          1
2010-1-4             1          1
2010-1-5             2          1
2010-1-6             3          1
2010-1-7             4          1
2010-1-8             5          2
2010-1-9             6          2
2010-1-10            0          2
2010-1-11            1          2
2010-1-12            2          2
2010-1-13            3          2
2010-1-14            4          2
2010-1-15            5          3
2010-1-16            6          3
2010-1-17            0          3
2010-1-18            1          3
2010-1-19            2          3
2010-1-20            3          3
2010-1-21            4          3
2010-1-22            5          4
2010-1-23            6          4
2010-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          1
2010-1-2             6          1
2010-1-3             0          1
2010-1-4             1          2
2010-1-5             2          2
2010-1-6             3          2
2010-1-7             4          2
2010-1-8             5          2
2010-1-9             6          2
2010-1-10            0          2
2010-1-11            1          3
2010-1-12            2          3
2010-1-13            3          3
2010-1-14            4          3
2010-1-15            5          3
2010-1-16            6          3
2010-1-17            0          3
2010-1-18            1          4
2010-1-19            2          4
2010-1-20            3          4
2010-1-21            4          4
2010-1-22            5          4
2010-1-23            6          4
2010-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条），并把这些天都转换成星期几（与实际星期几对应），最后把每个星期一都取出来进行累加，考虑到年初第一天可能不是周一，需要特殊处理外，其他直接相加即可

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-675027/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-675027/