这是我在论坛中某个帖子里见到的需求,想了一下,用几种方法解决了!
原帖需求:如下
我要算 一个月的 1号是 第一周
2号—8号是第二周
9号-15号是第三周
16号-22号是第四周
23-29号是第五周
其余的算第六周
如何写呢?
我最开始的方法:
with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d from dual connect by rownum<=365)
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t
这样有个人提出一个问题,那就是闰年是366天,不是365天,为此我又做了修改:
--方法一
with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d from dual
connect by rownum<=add_months(trunc(sysdate,'year'),12)-trunc(sysdate,'year'))
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t;
--方法二
with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d from dual
connect by rownum<=decode(to_char(last_day(to_date(to_char(sysdate,'yyyy')||02,'yyyymm')),'dd'),28,365,366))
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t;
--方法三
with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d from dual
connect by rownum<=(select case when mod(to_char(sysdate,'yyyy'),400)=0 or
(mod(to_char(sysdate,'yyyy'),4)=0 and mod(to_char(sysdate,'yyyy'),100)!=0) then
366 else 365 end dd
from dual))
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t;
--方法四
CREATE OR REPLACE
FUNCTION YearDays(
n IN NUMBER)
RETURN NUMBER
IS
v_mod4 NUMBER(6,2);
v_mod100 NUMBER(6,2);
v_mod400 NUMBER(6,2);
BEGIN
v_mod4 :=mod(n,4);
v_mod100 :=mod(n,100);
v_mod400 :=mod(n,400);
IF (v_mod400=0 OR (v_mod100<>0 AND v_mod4=0)) THEN
RETURN 366;
ELSE
RETURN 365;
END IF;
END;
with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d from dual connect by rownum<=YearDays(2012))
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t;
select d,decode(ceil((to_char(d,'dd')-1)/7),0,1,ceil((to_char(d,'dd')-1)/7)+1) w from t;
点评:以上四种方法都能处理闰年的天数问题,个人最喜欢的方法是方法二,因为偶一贯秉承能用一条SQL解决的尽量用SQL解决!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26977915/viewspace-734506/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26977915/viewspace-734506/