# SQL趣题：按某种区间规则返回今年每月的星期（注意闰年天数为366）

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

--方法一

with t as (select to_date('2012-01-01','yyyy-mm-dd')+rownum-1 d 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;

--方法二

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;

• 博文量
35
• 访问量
91948