抱歉工作原因回复的晚了点。。
因为需求实际是用在DB2上的。。。所以改成DB2的版本做了下试验。。DB2也支持with写法,但是不支持把with写在from后面。所以改了改。。换成oracle版本的写法如下,只是吧with移到上面去了而已:
create table tt_time(t date,d integer);
insert into tt_time values('1-2月-2010',10);
insert into tt_time values('6-2月-2010',10);
insert into tt_time values('7-2月-2010');
insert into tt_time values('8-2月-2010',10);
insert into tt_time values('9-2月-2010');
insert into tt_time values('10-2月-2010');
with tt
as
(SELECT
decode(to_char(to_date('2010-2-1','yyyy-mm-dd'),'DY'),'星期六',to_date('2010-2-1','yyyy-mm-dd'),
next_day(to_date('2010-2-1','yyyy-mm-dd'),'星期六')) t_2
from dual
union all
SELECT
decode(to_char(to_date('2010-2-1','yyyy-mm-dd'),'DY'),'星期六',to_date('2010-2-1','yyyy-mm-dd'),
next_day(to_date('2010-2-1','yyyy-mm-dd'),'星期六')) + 1
from dual
union all
SELECT
decode(to_char(to_date('2010-2-1','yyyy-mm-dd'),'DY'),'星期六',to_date('2010-2-1','yyyy-mm-dd'),
next_day(to_date('2010-2-1','yyyy-mm-dd'),'星期六')) + 2
from dual
union all
SELECT
decode(to_char(to_date('2010-2-1','yyyy-mm-dd'),'DY'),'星期六',to_date('2010-2-1','yyyy-mm-dd'),
next_day(to_date('2010-2-1','yyyy-mm-dd'),'星期六')) + 3
from dual)
select decode(to_char(tt.t_2,'d'),
7,to_char(tt.t_2,'mm')||'月'||to_char(tt.t_2,'dd')||'日'||'(周六)',
1,to_char(tt.t_2,'mm')||'月'||to_char(tt.t_2,'dd')||'日'||'(周日)',
2,to_char(tt.t_2,'mm')||'月'||to_char(tt.t_2,'dd')||'日'||'(周一)',
3,to_char(tt.t_2,'mm')||'月'||to_char(tt.t_2,'dd')||'日'||'(周二)',
null),
tt_time.d
from tt,tt_time
where tt.t_2 = tt_time.t(+)
order by tt.t_2
DB2版写法如下:
with tt(t_2)
as
(
select decode(DAYOFWEEK_ISO('2010-2-1'),7,date('2010-2-1') + 6 day,date('2010-2-1') + (6 - DAYOFWEEK_ISO('2010-2-1')) day)
from SYSIBM.SYSDUMMY1
UNION ALL
select decode(DAYOFWEEK_ISO('2010-2-1'),7,date('2010-2-1') + 6 day,date('2010-2-1') + (6 - DAYOFWEEK_ISO('2010-2-1')) day) + 1 day
from SYSIBM.SYSDUMMY1
UNION ALL
select decode(DAYOFWEEK_ISO('2010-2-1'),7,date('2010-2-1') + 6 day,date('2010-2-1') + (6 - DAYOFWEEK_ISO('2010-2-1')) day) + 2 day
from SYSIBM.SYSDUMMY1
UNION ALL
select decode(DAYOFWEEK_ISO('2010-2-1'),7,date('2010-2-1') + 6 day,date('2010-2-1') + (6 - DAYOFWEEK_ISO('2010-2-1')) day) + 3 day
from SYSIBM.SYSDUMMY1
)
select
decode(DAYOFWEEK_ISO(tt.t_2),6,month(tt.t_2)||'月'||day(tt.t_2)||'日'||'(周六)',7,month(tt.t_2)||'月'||day(tt.t_2)||'日'||'(周日)',1,month(tt.t_2)||'月'||day(tt.t_2)||'日'||'(周一)',2,month(tt.t_2)||'月'||day(tt.t_2)||'日'||'(周二)',null),
tt_time.d
from tt left outer join tt_time on tt.t_2 = tt_time.t
order by tt.t_2
|