WITH x AS
(select TO_DATE('2015','YYYY') qry_year from dual),
x0 AS --查询年份的第一天,当年12月28号
(SELECT TRUNC(qry_year,'YYYY') AS first_day,
to_date(to_char(qry_year,'yyyy')||'-12-28','yyyy-mm-dd') AS last_day
FROM x),
x1 AS --第一天为周几TO_CHAR(xx,'D')返回1-7(周日-周六)
(select TO_CHAR(x0.first_day,'D') week_idx from x0) ,
x2 AS --为了方便计算,改成2-8(周一-周日)
(select decode(week_idx,'1','8',week_idx) week_idx2 from x1),
x3 AS --是否多余4天
(select SIGN(5-week_idx2) flag from x2),
x4 AS --当-1时,年初几天为第一周,否则为上一年的末周
(select decode(flag,-1,0,-1) ratio from x3)
--NEXT_DAY(xx,2)返回下一个星期一的日期(1表示星期日,2代表星期一)
--to_char(last_day,'IW')当年12月28号为最大周数
select LEVEL,
NEXT_DAY(first_day+(LEVEL-1+ratio)*7,2) week_first_day,
NEXT_DAY(first_day+(LEVEL-1+ratio)*7,2)+6 week_last_day
from x0 left join x4 on 1=1
(select TO_DATE('2015','YYYY') qry_year from dual),
x0 AS --查询年份的第一天,当年12月28号
(SELECT TRUNC(qry_year,'YYYY') AS first_day,
to_date(to_char(qry_year,'yyyy')||'-12-28','yyyy-mm-dd') AS last_day
FROM x),
x1 AS --第一天为周几TO_CHAR(xx,'D')返回1-7(周日-周六)
(select TO_CHAR(x0.first_day,'D') week_idx from x0) ,
x2 AS --为了方便计算,改成2-8(周一-周日)
(select decode(week_idx,'1','8',week_idx) week_idx2 from x1),
x3 AS --是否多余4天
(select SIGN(5-week_idx2) flag from x2),
x4 AS --当-1时,年初几天为第一周,否则为上一年的末周
(select decode(flag,-1,0,-1) ratio from x3)
--NEXT_DAY(xx,2)返回下一个星期一的日期(1表示星期日,2代表星期一)
--to_char(last_day,'IW')当年12月28号为最大周数
select LEVEL,
NEXT_DAY(first_day+(LEVEL-1+ratio)*7,2) week_first_day,
NEXT_DAY(first_day+(LEVEL-1+ratio)*7,2)+6 week_last_day
from x0 left join x4 on 1=1
CONNECT BY LEVEL<=to_char(last_day,'IW');
/*IW是ISO标准周,它的含义是ISO标准周以周别为“主线”,每年最多可以有53个周别,
但是每年至少要包含52个周别;如果一年当中第52周别之后至当年的12月31日之间,
还有大于或等于4天的话,则定为当年的第53周,否则剩余这些天数被归为下一年的第1周;
如果在不足52周别的话,则以下一年的时间来补;每周固定的从周一开始作为本周的第1天,到周日作为本周的第7天;
比如:在Oracle中 2012年01月01号依然属于IW周别2011年的第52周的第7天。这个用到的比较多*/
参考http://blog.csdn.net/liangweiwei130/article/details/37930383