2013年（350）
分类： Mysql/postgreSQL
2013-04-25 10:05:18
临时想查日历，windows自带的只能按月一月一月翻，一时兴起，通过生成了一个全年的日历出来，语句如下：
SQL> select
2 min(to_char(day, 'yyyy-mm')) month,
3 min(decode(weekday, 1, day)) "Mon",
4 min(decode(weekday, 2, day)) "Tue",
5 min(decode(weekday, 3, day)) "Wed",
6 min(decode(weekday, 4, day)) "Thu",
7 min(decode(weekday, 5, day)) "Fri",
8 min(decode(weekday, 6, day)) "Sat",
9 min(decode(weekday, 7, day)) "Sun"
10 from (select day,
11 month,
12 decode(sign(rn - weekday), 1, week + 1, week) week,
13 weekday,
14 rn
15 from (select day,
16 to_char(day, 'mm') month,
17 to_char(day, 'w') week,
18 to_char(day, 'd') weekday,
19 row_number() over(partition by to_char(day, 'mm'), to_char(day, 'w') order by day) rn
20 from (select trunc(sysdate, 'yyyy') + level - 1 day
21 from dual
22 connect by rownum <= trunc(sysdate+365,'yyyy')-trunc(sysdate,'yyyy')))) a
23 group by a.month, a.week
24 order by a.month, a.week
25 ;
MONTH Mon Tue Wed Thu Fri Sat Sun
------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2008-01 2008-01-01 2008-01-02 2008-01-03 2008-01-04 2008-01-05
2008-01 2008-01-06 2008-01-07 2008-01-08 2008-01-09 2008-01-10 2008-01-11 2008-01-12
2008-01 2008-01-13 2008-01-14 2008-01-15 2008-01-16 2008-01-17 2008-01-18 2008-01-19
2008-01 2008-01-20 2008-01-21 2008-01-22 2008-01-23 2008-01-24 2008-01-25 2008-01-26
2008-01 2008-01-27 2008-01-28 2008-01-29 2008-01-30 2008-01-31
2008-02 2008-02-01 2008-02-02
2008-02 2008-02-03 2008-02-04 2008-02-05 2008-02-06 2008-02-07 2008-02-08 2008-02-09
2008-02 2008-02-10 2008-02-11 2008-02-12 2008-02-13 2008-02-14 2008-02-15 2008-02-16
2008-02 2008-02-17 2008-02-18 2008-02-19 2008-02-20 2008-02-21 2008-02-22 2008-02-23
2008-02 2008-02-24 2008-02-25 2008-02-26 2008-02-27 2008-02-28 2008-02-29
2008-03 2008-03-01
2008-03 2008-03-02 2008-03-03 2008-03-04 2008-03-05 2008-03-06 2008-03-07 2008-03-08
2008-03 2008-03-09 2008-03-10 2008-03-11 2008-03-12 2008-03-13 2008-03-14 2008-03-15
2008-03 2008-03-16 2008-03-17 2008-03-18 2008-03-19 2008-03-20 2008-03-21 2008-03-22
2008-03 2008-03-23 2008-03-24 2008-03-25 2008-03-26 2008-03-27 2008-03-28 2008-03-29
2008-03 2008-03-30 2008-03-31
2008-04 2008-04-01 2008-04-02 2008-04-03 2008-04-04 2008-04-05
2008-04 2008-04-06 2008-04-07 2008-04-08 2008-04-09 2008-04-10 2008-04-11 2008-04-12
2008-04 2008-04-13 2008-04-14 2008-04-15 2008-04-16 2008-04-17 2008-04-18 2008-04-19
2008-04 2008-04-20 2008-04-21 2008-04-22 2008-04-23 2008-04-24 2008-04-25 2008-04-26
2008-04 2008-04-27 2008-04-28 2008-04-29 2008-04-30
2008-05 2008-05-01 2008-05-02 2008-05-03
2008-05 2008-05-04 2008-05-05 2008-05-06 2008-05-07 2008-05-08 2008-05-09 2008-05-10
2008-05 2008-05-11 2008-05-12 2008-05-13 2008-05-14 2008-05-15 2008-05-16 2008-05-17
2008-05 2008-05-18 2008-05-19 2008-05-20 2008-05-21 2008-05-22 2008-05-23 2008-05-24
2008-05 2008-05-25 2008-05-26 2008-05-27 2008-05-28 2008-05-29 2008-05-30 2008-05-31
2008-06 2008-06-01 2008-06-02 2008-06-03 2008-06-04 2008-06-05 2008-06-06 2008-06-07
2008-06 2008-06-08 2008-06-09 2008-06-10 2008-06-11 2008-06-12 2008-06-13 2008-06-14
2008-06 2008-06-15 2008-06-16 2008-06-17 2008-06-18 2008-06-19 2008-06-20 2008-06-21
2008-06 2008-06-22 2008-06-23 2008-06-24 2008-06-25 2008-06-26 2008-06-27 2008-06-28
2008-06 2008-06-29 2008-06-30
2008-07 2008-07-01 2008-07-02 2008-07-03 2008-07-04 2008-07-05
2008-07 2008-07-06 2008-07-07 2008-07-08 2008-07-09 2008-07-10 2008-07-11 2008-07-12
2008-07 2008-07-13 2008-07-14 2008-07-15 2008-07-16 2008-07-17 2008-07-18 2008-07-19
2008-07 2008-07-20 2008-07-21 2008-07-22 2008-07-23 2008-07-24 2008-07-25 2008-07-26
2008-07 2008-07-27 2008-07-28 2008-07-29 2008-07-30 2008-07-31
2008-08 2008-08-01 2008-08-02
2008-08 2008-08-03 2008-08-04 2008-08-05 2008-08-06 2008-08-07 2008-08-08 2008-08-09
2008-08 2008-08-10 2008-08-11 2008-08-12 2008-08-13 2008-08-14 2008-08-15 2008-08-16
2008-08 2008-08-17 2008-08-18 2008-08-19 2008-08-20 2008-08-21 2008-08-22 2008-08-23
2008-08 2008-08-24 2008-08-25 2008-08-26 2008-08-27 2008-08-28 2008-08-29 2008-08-30
2008-08 2008-08-31
2008-09 2008-09-01 2008-09-02 2008-09-03 2008-09-04 2008-09-05 2008-09-06
2008-09 2008-09-07 2008-09-08 2008-09-09 2008-09-10 2008-09-11 2008-09-12 2008-09-13
2008-09 2008-09-14 2008-09-15 2008-09-16 2008-09-17 2008-09-18 2008-09-19 2008-09-20
2008-09 2008-09-21 2008-09-22 2008-09-23 2008-09-24 2008-09-25 2008-09-26 2008-09-27
2008-09 2008-09-28 2008-09-29 2008-09-30
2008-10 2008-10-01 2008-10-02 2008-10-03 2008-10-04
2008-10 2008-10-05 2008-10-06 2008-10-07 2008-10-08 2008-10-09 2008-10-10 2008-10-11
2008-10 2008-10-12 2008-10-13 2008-10-14 2008-10-15 2008-10-16 2008-10-17 2008-10-18
2008-10 2008-10-19 2008-10-20 2008-10-21 2008-10-22 2008-10-23 2008-10-24 2008-10-25
2008-10 2008-10-26 2008-10-27 2008-10-28 2008-10-29 2008-10-30 2008-10-31
2008-11 2008-11-01
2008-11 2008-11-02 2008-11-03 2008-11-04 2008-11-05 2008-11-06 2008-11-07 2008-11-08
2008-11 2008-11-09 2008-11-10 2008-11-11 2008-11-12 2008-11-13 2008-11-14 2008-11-15
2008-11 2008-11-16 2008-11-17 2008-11-18 2008-11-19 2008-11-20 2008-11-21 2008-11-22
2008-11 2008-11-23 2008-11-24 2008-11-25 2008-11-26 2008-11-27 2008-11-28 2008-11-29
2008-11 2008-11-30
2008-12 2008-12-01 2008-12-02 2008-12-03 2008-12-04 2008-12-05 2008-12-06
2008-12 2008-12-07 2008-12-08 2008-12-09 2008-12-10 2008-12-11 2008-12-12 2008-12-13
2008-12 2008-12-14 2008-12-15 2008-12-16 2008-12-17 2008-12-18 2008-12-19 2008-12-20
2008-12 2008-12-21 2008-12-22 2008-12-23 2008-12-24 2008-12-25 2008-12-26 2008-12-27
2008-12 2008-12-28 2008-12-29 2008-12-30 2008-12-31
