17,086
社区成员
发帖
与我相关
我的任务
分享
Select tdate, week
From (Select to_char(c1, 'yyyymmdd') tdate,
decode(to_char(c1, 'd'),
'1',
'seven',
'2',
'one',
'3',
'two',
'4',
'three',
'5',
'four',
'6',
'five',
'7',
'six') week
From (
--得出每个周日和往前推6天的日期
Select Distinct c1 - Level + 1 c1
From (
--根据最小的日期一次往前推3周
Select c1 + (3 * 7) *
(Level - 1) c1
From (
--计算为周日的最小日期
Select Min(c1) c1
From (
--构造4月1号到9月1号的日期表
Select to_date('2013-04-01','yyyy-mm-dd') + Level - 1 c1
From dual
Connect By Level <=
to_date('2013-09-01','yyyy-mm-dd') - to_date('2013-04-01','yyyy-mm-dd') + 1)
Where to_char(c1, 'd') = 1)
Connect By Level <=
(to_date('2013-09-01','yyyy-mm-dd') - to_date('2013-04-01','yyyy-mm-dd')) /
(3 * 7) + 1)
Connect By Level < 8
Order By c1)
Where c1 >= to_date('2013-04-01','yyyy-mm-dd'))q
Where Exists (Select week
From ( /**将某字段,由逗行分隔的值转成列**/
Select REGEXP_SUBSTR('one,four', '[^,]+', 1, rownum) As week
From DUAL
Connect By ROWNUM <= LENGTH('one,four') -
LENGTH(Replace('one,four', ',')) + 1) w
Where w.week = q.week)