sql实现当月日历

wngsheng

select max(su) su,max(mo) mo,max(tu) tu,max(we) we,max(th) th,max(fr) fr,max(sa) sa from
(select case when d=1 then dd end su,
       case when d=2 then dd end mo,
       case when d=3 then dd end tu,
       case when d=4 then dd end we,
       case when d=5 then dd end th,
       case when d=6 then dd end fr,
       case when d=7 then dd end sa,
       d,w,iw
from(
select dates,to_char(dates,'dd') dd,to_number(to_char(dates,'d')) d,to_char(dates,'w') w,to_char(dates+1,'iw') iw from
(select trunc(sysdate,'month')+level-1 dates from dual
connect by rownum<last_day(sysdate)-trunc(sysdate,'month')
)
)
)
group by iw
order by iw;
[/COLOR]

环境：oracle 10g

junsansi

最初由 wngsheng 发布
[B]
select max(su) su,max(mo) mo,max(tu) tu,max(we) we,max(th) th,max(fr) fr,max(sa) sa from
(select case when d=1 then dd end su,
       case when d=2 then dd end mo,
       case when d=3 then dd end tu,
       case when d=4 then dd end we,
       case when d=5 then dd end th,
       case when d=6 then dd end fr,
       case when d=7 then dd end sa,
       d,w,iw
from(
select dates,to_char(dates,'dd') dd,to_number(to_char(dates,'d')) d,to_char(dates,'w') w,to_char(dates+1,'iw') iw from
(select trunc(sysdate,'month')+level-1 dates from dual
connect by rownum<last_day(sysdate)-trunc(sysdate,'month')
)
)
)
group by iw
order by iw;
[/COLOR]

环境：oracle 10g [/B]

不错，不过w和iw留1个即可

hanjs

不错，不过都有查出一年的了！

jellywang

牛

lixuezhen

最初由 wngsheng 发布
[B]
select max(su) su,max(mo) mo,max(tu) tu,max(we) we,max(th) th,max(fr) fr,max(sa) sa from
(select case when d=1 then dd end su,
       case when d=2 then dd end mo,
       case when d=3 then dd end tu,
       case when d=4 then dd end we,
       case when d=5 then dd end th,
       case when d=6 then dd end fr,
       case when d=7 then dd end sa,
       d,w,iw
from(
select dates,to_char(dates,'dd') dd,to_number(to_char(dates,'d')) d,to_char(dates,'w') w,to_char(dates+1,'iw') iw from
(select trunc(sysdate,'month')+level-1 dates from dual
connect by rownum<last_day(sysdate)-trunc(sysdate,'month')
)
)
)
group by iw
order by iw;
[/COLOR]

环境：oracle 10g [/B]

代码格式缩进一下，看看清楚都好啊
唉

goodhawk

非常好。以前也想写，没写出来。

稍微纠正一下。 最后那个要 ＋ 1
LAST_DAY (SYSDATE)- TRUNC (SYSDATE, 'month') + 1

kso

9i下也适用..

pqxpqx

创意不错，楼主真牛。

wangfans

牛尹~~~~

sudan521

select max(su) su,max(mo) mo,max(tu) tu,max(we) we,max(th) th,max(fr) fr,max(sa) sa from
(select case when d=1 then dd end su,
case when d=2 then dd end mo,
case when d=3 then dd end tu,
case when d=4 then dd end we,
case when d=5 then dd end th,
case when d=6 then dd end fr,
case when d=7 then dd end sa,
d,w
from(
select dates,to_char(dates,'dd') dd,to_number(to_char(dates,'d')) d,to_char(dates,'w') w,to_char(dates+1,'iw') iw from
(select trunc(sysdate,'month')+level-1 dates from dual
connect by rownum<last_day(sysdate)-trunc(sysdate,'month')+1
)
)
)
group by w
order by w;

//8月
SU MO TU WE TH FR SA
-- -- -- -- -- -- --
05 06 07 01 02 03 04
12 13 14 08 09 10 11
19 20 21 15 16 17 18
26 27 28 22 23 24 25
          29 30 31

//9月

SU MO TU WE TH FR SA
-- -- -- -- -- -- --
02 03 04 05 06 07 01
09 10 11 12 13 14 08
16 17 18 19 20 21 15
23 24 25 26 27 28 22
30                          29