创建周列表，将类别划分为月间隔

Question

我需要创建一个53周的列表，分为几个月(这些是零售月份，所以它们对零售业的人来说更有意义)。

我需要重复这个列表长达53周，间隔4-5-4。

有关示例，请参阅sql fiddle

我想要在SQL语句中编写这段代码，而不是使用表，也不想使用大量的联合。我确信有一种使用rownum和connect-by的方法，但我就是想不出其中的逻辑！

Answer 1

我最终使用了以下代码。不是很理想，但它是有效的：

SELECT ROWNUM AS WEEK_ID, 1 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+4 AS WEEK_ID, 2 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 5
UNION ALL
SELECT ROWNUM+9 AS WEEK_ID, 3 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+13 AS WEEK_ID, 4 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+17 AS WEEK_ID, 5 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 5
UNION ALL
SELECT ROWNUM+22 AS WEEK_ID, 6 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+26 AS WEEK_ID, 7 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+30 AS WEEK_ID, 8 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 5
UNION ALL
SELECT ROWNUM+35 AS WEEK_ID, 9 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+39 AS WEEK_ID, 10 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 4
UNION ALL
SELECT ROWNUM+43 AS WEEK_ID, 11 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 5
UNION ALL
SELECT ROWNUM+48 AS WEEK_ID, 12 AS MONTH_ID
FROM DUAL
CONNECT BY ROWNUM <= 5

Answer 2

这是我的尝试：

SELECT DISTINCT to_char(l_date,'WW') week_id ,
      to_char(l_date, 'mm') month_id
FROM
     (SELECT to_date('01/jan/2013', 'dd/mm/yyyy')+LEVEL-1 l_date
     FROM dual
          CONNECT BY LEVEL <= 365
     ORDER BY 1
     )
ORDER BY WEEK_ID;

根据您的SQL表编辑：

SELECT week_id,     MAX(month_id)
FROM
     (SELECT DISTINCT TO_CHAR(l_date,'WW') week_id ,
          TO_CHAR(l_date, 'mm') month_id
     FROM
          (SELECT TRUNC(SYSDATE, 'year')+LEVEL-1 l_date
          FROM dual
               CONNECT BY LEVEL <= 365
          )
     )
GROUP BY week_id 
     ORDER BY WEEK_ID;