用一条SQL语句做一个星期月历

select sum(decode(eachday, 0, dayid, NULL)) "日",
sum(decode(eachday, 1, dayid, NULL)) "一",
sum(decode(eachday, 2, dayid, NULL)) "二",
sum(decode(eachday, 3, dayid, NULL)) "三",
sum(decode(eachday, 4, dayid, NULL)) "四",
sum(decode(eachday, 5, dayid, NULL)) "五",
sum(decode(eachday, 6, dayid, NULL)) "六" from (
select monthday, to_number(to_char(monthday, 'dd')) as dayid, weekday, round((weekday+3.5) / 7,0) weekid, mod(weekday+7, 7 ) eachday from (
select trunc(sysdate,'month')+rownum -1  as monthday, trunc(sysdate,'month')+rownum +6  - next_day(trunc(sysdate,'month'),1) as weekday from all_objects
where rownum <= ( select to_char(last_day( sysdate ),'dd') from dual ) )
)
group by weekid;

这个就是结果,兄弟们看看,有什么新想法:)

先顶一个再说！(*^__^*) ！

修改一下：
加个年月日：
select sum(decode(eachday, 0, dayid, NULL)) "日",
sum(decode(eachday, 1, dayid, NULL)) "一",
sum(decode(eachday, 2, dayid, NULL)) "二",
sum(decode(eachday, 3, dayid, NULL)) "三",
sum(decode(eachday, 4, dayid, NULL)) "四",
sum(decode(eachday, 5, dayid, NULL)) "五",
sum(decode(eachday, 6, dayid, NULL)) "六" from (
select monthday, to_number(to_char(monthday, 'dd')) as dayid, weekday, round((weekday+3.5) / 7,0) weekid, mod(weekday+7, 7 ) eachday from (
select trunc(sysdate,'month')+rownum -1  as monthday, trunc(sysdate,'month')+rownum +6  - next_day(trunc(sysdate,'month'),1) as weekday from all_objects
where rownum <= ( select to_char(last_day( sysdate ),'dd') from dual ) )
)
group by weekid
union all
select null,null,null,null,null,null,to_number(to_char(sysdate,'yyyymmdd')) from dual

。。。。。。。。。。

[[i] 本帖最后由 zhangfengh 于 2008-7-6 21:48 编辑 [/i]]

改得有点意思,以此为由,我写过一篇文章,发表在<软件报>上的

我的原文在此:
[url=http://blog.sina.com.cn/s/blog_511846a201008yw3.html]http://blog.sina.com.cn/s/blog_511846a201008yw3.html[/url]

[quote]原帖由 [i]sunfly1983[/i] 于 2008-7-6 21:09 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10872415&ptid=1017537][img]http://www.itpub.net/images/common/back.gif[/img][/url]
修改一下：
加个年月日：
select sum(decode(eachday, 0, dayid, NULL)) "日",
sum(decode(eachday, 1, dayid, NULL)) "一",
sum(decode(eachday, 2, dayid, NULL)) "二",
sum(decode(eachday, 3, dayid, NULL)) "三",
sum(decode(eachday, 4, dayid, NULL)) "四",
sum(decode(eachday, 5, dayid, NULL)) "五",
sum(decode(eachday, 6, dayid, NULL)) "六" from (
select monthday, to_number(to_char(monthday, 'dd')) as dayid, weekday, round((weekday+3.5) / 7,0) weekid, mod(weekday+7, 7 ) eachday from (
select trunc(sysdate,'month')+rownum -1  as monthday, trunc(sysdate,'month')+rownum +6  - next_day(trunc(sysdate,'month'),1) as weekday from all_objects
where rownum  [/quote]

一個字：牛！

[url]http://space.itpub.net/7607759/viewspace-220372[/url]

select   
        max(decode(mod(n,7),0,m,null))  日,
        max(decode(mod(n,7),1,m,null))  一,
        max(decode(mod(n,7),2,m,null))  二,
        max(decode(mod(n,7),3,m,null))  三,
        max(decode(mod(n,7),4,m,null))  四,
        max(decode(mod(n,7),5,m,null))  五,
        max(decode(mod(n,7),6,m,null))  六
from
    (  select rownum m, rownum+to_char(trunc(sysdate,'mm'),'d')-2 n
       from  dual
       connect by rownum< = to_char(last_day(sysdate),'dd') )
group by trunc(n/7)
order by trunc(n/7)

牛

学习了

年历：
[url]http://www.itpub.net/thread-469543-1-1.html[/url]

加强一下 当天有星号标示

select   
        max(decode(mod(n,7),0,decode(f,1,'*'||m||'*',m),null))  日,
        max(decode(mod(n,7),1,decode(f,1,'*'||m||'*',m),null))  一,
        max(decode(mod(n,7),2,decode(f,1,'*'||m||'*',m),null))  二,
        max(decode(mod(n,7),3,decode(f,1,'*'||m||'*',m),null))  三,
        max(decode(mod(n,7),4,decode(f,1,'*'||m||'*',m),null))  四,
        max(decode(mod(n,7),5,decode(f,1,'*'||m||'*',m),null))  五,
        max(decode(mod(n,7),6,decode(f,1,'*'||m||'*',m),null))  六
from
    (select rownum m, rownum+to_char(trunc(sysdate,'mm'),'d')-2 n,
            decode(rownum,TO_NUMBER(to_CHAR(sysdate,'dd')),1,0) F
       from  dual
    connect by rownum< = to_char(last_day(sysdate),'dd') )
group by trunc(n/7)
order by trunc(n/7)

[quote]原帖由 [i]junsansi[/i] 于 2008-7-7 09:08 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10874156&ptid=1017537][img]http://www.itpub.net/images/common/back.gif[/img][/url]
[url]http://space.itpub.net/7607759/viewspace-220372[/url] [/quote]

看了三思的代码，
把connect by rownum <= trunc(sysdate+365,'yyyy')-trunc(sysdate,'yyyy')改成这样就不用考虑一年是365还是366了
connect by rownum <= trunc(sysdate+ Interval '1' Year,'yyyy')-trunc(sysdate,'yyyy')

回复 #13 jvkojvko 的帖子

借用  jvkojvko 的  trunc(sysdate+ Interval '1' Year,'yyyy')-trunc(sysdate,'yyyy')
  整个年的日历
  select  mm 月份,
        max(decode(mod(d,7),0,dd,null))  日,
        max(decode(mod(d,7),1,dd,null))  一,
        max(decode(mod(d,7),2,dd,null))  二,
        max(decode(mod(d,7),3,dd,null))  三,
        max(decode(mod(d,7),4,dd,null))  四,
        max(decode(mod(d,7),5,dd,null))  五,
        max(decode(mod(d,7),6,dd,null))  六
     from
   ( select to_char(d,'yyyy-mm')     mm ,
            to_char(d,'dd')          dd ,
            to_char(d,'dd')+to_char(trunc(d,'mm'),'d')-2  d
    from
   ( select trunc(sysdate,'y')+rownum-1 d from dual
     connect by rownum< =trunc(sysdate+ Interval '1' Year,'yyyy')-trunc(sysdate,'yyyy') ))
    group  by mm,trunc(d/7)
   order by  mm,trunc(d/7)

[[i] 本帖最后由 grubbyoo 于 2008-7-7 11:02 编辑 [/i]]

呵呵，有意思

回复 #14 grubbyoo 的帖子

不错，要是加个阴历，就更有难度了。

[quote]原帖由 [i]grubbyoo[/i] 于 2008-7-7 10:05 发表 [url=http://www.itpub.net/redirect.php?goto=findpost&pid=10875010&ptid=1017537][img]http://www.itpub.net/images/common/back.gif[/img][/url]
select   
        max(decode(mod(n,7),0,m,null))  日,
        max(decode(mod(n,7),1,m,null))  一,
        max(decode(mod(n,7),2,m,null))  二,
        max(decode(mod(n,7),3,m,null))  三,
        max(decode(mod(n,7),4,m,null))  四,
        max(decode(mod(n,7),5,m,null))  五,
        max(decode(mod(n,7),6,m,null))  六
from
    (  select rownum m, rownum+to_char(trunc(sysdate,'mm'),'d')-2 n
       from  dual
       connect by rownum< = to_char(last_day(sysdate),'dd') )
group by trunc(n/7)
order by trunc(n/7) [/quote]

强,看来群众的力量还是无限的

对不起了．借例子的ＳＱＬ，顺便问点问题．
为什么我单独执行
select rownum m, rownum+to_char(trunc(sysdate,'mm'),'d')-2 n
from  dual
connect by rownum< = to_char(last_day(sysdate),'dd')
只出现一条结果？dual返回行数怎么限制的．全部执行没问题！

这里connect by 是不是有点end while(false)的意思？
start with可有可无吗？
附：
CONNECT BY
格式：select expression from table
where condition
connect by [prior] expression= [prior] expression
start with expression = expression
order by expression
描述：CONNECT BY是一个运算符，在select 语句中用来建交反映树形结构数据，
START　WITH指示从树的何处开始。

學習

学习了