with x0 as(select to_date('2011-03-12','yyyy-mm-dd') dt,rownum-1 num from sys.error$ where rownum<32),
x1 as(select to_char(trunc(dt,'month')+num,'d')-1 wk ,
ceil((to_char(trunc(dt,'month')+num,'ddd')+to_char(to_date(to_char(dt,'yyyy')||'0101','yyyymmdd'),'d')-1)/7) ww,
to_char(num,'00')+1 dd from x0 where trunc(dt,'month')+num <= last_day(dt) )
SELECT ww "周",
MAX(decode(wk, '0', dd, null)) "日",
MAX(decode(wk, '1', dd, null)) "一",
MAX(decode(wk, '2', dd, null)) "二",
MAX(decode(wk, '3', dd, null)) "三",
MAX(decode(wk, '4', dd, null)) "四",
MAX(decode(wk, '5', dd, null)) "五",
MAX(decode(wk, '6', dd, null)) "六"
FROM x1
group by ww
周 日 一 二 三 四 五 六
10 1 2 3 4 5
11 6 7 8 9 10 11 12
12 13 14 15 16 17 18 19
13 20 21 22 23 24 25 26
14 27 28 29 30 31
max(decode(to_char(dt,'day'),'星期日',to_char(dt,'dd'))) "星期日",
max(decode(to_char(dt,'day'),'星期一',to_char(dt,'dd'))) "星期一",
max(decode(to_char(dt,'day'),'星期二',to_char(dt,'dd'))) "星期二",
max(decode(to_char(dt,'day'),'星期三',to_char(dt,'dd'))) "星期三",
max(decode(to_char(dt,'day'),'星期四',to_char(dt,'dd'))) "星期四",
max(decode(to_char(dt,'day'),'星期五',to_char(dt,'dd'))) "星期五",
max(decode(to_char(dt,'day'),'星期六',to_char(dt,'dd'))) "星期六"
from
(select
to_char(dt,'d')-row_number() over(order by dt) gcol,
dt
from
(select trunc(to_date('2011-03-12','yyyy-mm-dd'),'mm')+level-1 dt
from dual
connect by level<=to_char(last_day(to_date('2010-12-15','yyyy-mm-dd')),'dd')))
group by gcol
order by abs(gcol)
SQL> select max(to_char(dt,'ww')) 周数,
2 max(decode(to_char(dt,'day'),'星期日',to_char(dt,'dd'))) "星期日",
3 max(decode(to_char(dt,'day'),'星期一',to_char(dt,'dd'))) "星期一",
4 max(decode(to_char(dt,'day'),'星期二',to_char(dt,'dd'))) "星期二",
5 max(decode(to_char(dt,'day'),'星期三',to_char(dt,'dd'))) "星期三",
6 max(decode(to_char(dt,'day'),'星期四',to_char(dt,'dd'))) "星期四",
7 max(decode(to_char(dt,'day'),'星期五',to_char(dt,'dd'))) "星期五",
8 max(decode(to_char(dt,'day'),'星期六',to_char(dt,'dd'))) "星期六"
9 from
10 (select
11 to_char(dt,'d')-row_number() over(order by dt) gcol,
12 dt
13 from
14 (select trunc(to_date('2011-03-12','yyyy-mm-dd'),'mm')+level-1 dt
15 from dual
16 connect by level<=to_char(last_day(to_date('2010-12-15','yyyy-mm-dd')),'dd')))
17 group by gcol
18 order by abs(gcol)
19 /
周数 星期日 星期一 星期二 星期三 星期四 星期五 星期六
---- ------ ------ ------ ------ ------ ------ ------
10 01 02 03 04 05
11 06 07 08 09 10 11 12
12 13 14 15 16 17 18 19
13 20 21 22 23 24 25 26
13 27 28 29 30 31
x1 as(select to_char(trunc(dt,'month')+num,'d')-1 wk ,
ceil((to_char(trunc(dt,'month')+num,'ddd')+to_char(to_date(to_char(dt,'yyyy')||'0101','yyyymmdd'),'d')-1)/7) ww,
to_char(num,'00')+1 dd
from x0 where trunc(dt,'month')+num <= last_day(dt) )
SELECT ww "周",
MAX(decode(wk, '0', dd, null)) "日",
MAX(decode(wk, '1', dd, null)) "一",
MAX(decode(wk, '2', dd, null)) "二",
MAX(decode(wk, '3', dd, null)) "三",
MAX(decode(wk, '4', dd, null)) "四",
MAX(decode(wk, '5', dd, null)) "五",
MAX(decode(wk, '6', dd, null)) "六"
FROM x1
group by ww<?xml version="1.0" ?>
<ROWDATA><ROW>
<&_21608;>10</&_21608;>
<&_26085;></&_26085;>
<&_19968;></&_19968;>
<&_20108;>1</&_20108;>
<&_19977;>2</&_19977;>
<&_22235;>3</&_22235;>
<&_20116;>4</&_20116;>
<&_20845;>5</&_20845;>
</ROW><ROW>
<&_21608;>11</&_21608;>
<&_26085;>6</&_26085;>
<&_19968;>7</&_19968;>
<&_20108;>8</&_20108;>
<&_19977;>9</&_19977;>
<&_22235;>10</&_22235;>
<&_20116;>11</&_20116;>
<&_20845;>12</&_20845;>
</ROW><ROW>
<&_21608;>12</&_21608;>
<&_26085;>13</&_26085;>
<&_19968;>14</&_19968;>
<&_20108;>15</&_20108;>
<&_19977;>16</&_19977;>
<&_22235;>17</&_22235;>
<&_20116;>18</&_20116;>
<&_20845;>19</&_20845;>
</ROW><ROW>
<&_21608;>13</&_21608;>
<&_26085;>20</&_26085;>
<&_19968;>21</&_19968;>
<&_20108;>22</&_20108;>
<&_19977;>23</&_19977;>
<&_22235;>24</&_22235;>
<&_20116;>25</&_20116;>
<&_20845;>26</&_20845;>
</ROW><ROW>
<&_21608;>14</&_21608;>
<&_26085;>27</&_26085;>
<&_19968;>28</&_19968;>
<&_20108;>29</&_20108;>
<&_19977;>30</&_19977;>
<&_22235;>31</&_22235;>
<&_20116;></&_20116;>
<&_20845;></&_20845;>
</ROW></ROWDATA>
SELECT CASE
WHEN (NEW_YWEEK = MIN(NEW_YWEEK)
OVER(PARTITION BY MON ORDER BY NEW_YWEEK)
)
THEN
MON_NAME
ELSE
NULL
END AS MONTH,
NEW_YWEEK AS YWEEK,
ROW_NUMBER() OVER(PARTITION BY MON ORDER BY NEW_YWEEK) AS MWEEK,
SUM(DECODE(WDAY, '1', MDAY, NULL)) AS SUN,
SUM(DECODE(WDAY, '2', MDAY, NULL)) AS MON,
SUM(DECODE(WDAY, '3', MDAY, NULL)) AS TUE,
SUM(DECODE(WDAY, '4', MDAY, NULL)) AS WED,
SUM(DECODE(WDAY, '5', MDAY, NULL)) AS THU,
SUM(DECODE(WDAY, '6', MDAY, NULL)) AS FRI,
SUM(DECODE(WDAY, '7', MDAY, NULL)) AS SAT
FROM (SELECT DAYOFYEAR AS EVERYDAY,
TO_CHAR(DAYOFYEAR, 'mm') AS MON,
TO_CHAR(DAYOFYEAR, 'Month') AS MON_NAME,
TO_CHAR(DAYOFYEAR, 'w') AS MWEEK,
TO_CHAR(DAYOFYEAR, 'ww') AS YWEEK,
CASE
WHEN (TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd') > '1')
AND (TO_CHAR(DAYOFYEAR, 'd') < TO_CHAR(TO_DATE(&YEAR || '0101', 'yyyymmdd'), 'd'))
THEN TO_CHAR(TO_CHAR(DAYOFYEAR, 'ww') + 1, 'fm00')
ELSE
TO_CHAR(DAYOFYEAR, 'ww')
END AS NEW_YWEEK,
TO_CHAR(DAYOFYEAR, 'd') AS WDAY,
TO_CHAR(DAYOFYEAR, 'dd') AS MDAY
FROM (SELECT TO_DATE(&YEAR || '0101', 'yyyymmdd') + LEVEL - 1 AS DAYOFYEAR FROM DUAL
CONNECT BY LEVEL <= TO_CHAR( TO_DATE(&YEAR || '1231', 'yyyymmdd'),'ddd' )
)
)
GROUP BY MON, MON_NAME, NEW_YWEEK;