PL/SQL獲取未來6個月內一年中的實際周數

如何獲得未來 6 個月一年中的實際周數？

示例：
13.09.2022 是一年中的第 38 周 17.09.2022 是一年
中的第 38 周
19.09.2022 是一年中的第 39 周
。
.
01.01.2023 是一年中的第一周（星期日）
02.01.2023 是一年中的第二周（星期一）
09.01.2023 是一年中的第三周
。
未來 6 個月

我已經知道一年中的實際周數，但不知道接下來的 6 個月，只是在下面的代碼中到年底。

SELECT LISTAGG (
                         DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm09')    week_num
                FROM (SELECT mydate,
                               (  (  TRUNC (mydate, 'day')
                                   - TRUNC (TRUNC (mydate, 'yyyy'), 'day'))
                                / 7)
                               CASE
                                   WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') =
                                        'sun'
                                   THEN
                                       1
                                   ELSE
                                       0
                               END    AS nr_of_sundays
                        FROM (    SELECT TRUNC (SYSDATE, 'yy') - 1   LEVEL    AS mydate
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 12),
                                                  'yy')
                                         - TRUNC (SYSDATE, 'yy')))
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm09')
            ORDER BY TO_CHAR (nr_of_sundays   1, 'fm09');

上述代碼的結果

你們能幫幫我嗎？

uj5u.com熱心網友回復：

以下是將原始查詢修改為僅顯示接下來 6 個月的 6 個月資料，從當月的第一天開始：

SELECT LISTAGG (
                         DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm09')    week_num
                FROM (SELECT mydate,
                               (  (  TRUNC (mydate, 'day')
                                   - TRUNC (TRUNC (mydate, 'yyyy'), 'day'))
                                / 7)
                               CASE
                                   WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') =
                                        'sun'
                                   THEN
                                       1
                                   ELSE
                                       0
                               END    AS nr_of_sundays
                        FROM (    SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL    AS mydate --
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), --
                                                  'mm') --
                                         - TRUNC (SYSDATE, 'mm'))) --
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm09')
            ORDER BY TO_CHAR (nr_of_sundays   1, 'fm09');
            
            SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL    AS mydate 
                                    FROM DUAL
                              CONNECT BY LEVEL <=
                                           TRUNC (ADD_MONTHS (SYSDATE, 6), 
                                                  'mm') 
                                         - TRUNC (SYSDATE, 'mm');

MONTHS      WEEK_NUM

JANUARY     01
JANUARY     02
JANUARY     03
JANUARY     04
FEBRUARY , JANUARY      05
FEBRUARY    06
FEBRUARY    07
FEBRUARY    08
FEBRUARY    09
SEPTEMBER   38
SEPTEMBER   39
OCTOBER  , SEPTEMBER    40
...

但是，因為結果只有“months”和“week_num”列，并且順序是 week_num - 它看起來很奇怪。第一行將是明年的第一個 week_num。您可以將年份添加到每一行。這是一個重寫的查詢，使用完全相同的邏輯，但包括每一行的年份：

WITH date_range (mydate) AS
(
   SELECT TRUNC (SYSDATE, 'mm') - 1   LEVEL
     FROM DUAL
         CONNECT BY LEVEL <=  TRUNC (ADD_MONTHS (SYSDATE, 6),'mm') - TRUNC (SYSDATE, 'mm')
), weeknum_per_date (mydate, nr_of_sundays) AS
(
   SELECT mydate,
          (TRUNC (mydate, 'day') - TRUNC (TRUNC (mydate, 'yyyy'), 'day')) / 7
            CASE WHEN TO_CHAR (TRUNC (mydate, 'YYYY'), 'day') = 'sun' THEN 1 ELSE 0 END    AS nr_of_sundays 
     FROM date_range
)
SELECT TO_CHAR(mydate, 'YYYY') as year, LISTAGG (DISTINCT TO_CHAR (TRUNC (mydate, 'MONTH'), 'MONTH'),
                         ', ')                              AS MONTHS,
                     TO_CHAR (nr_of_sundays   1, 'fm00')    week_num
  FROM weeknum_per_date                                            
               WHERE TO_DATE (mydate, 'DD/MM/YY') >=
                     TO_DATE (SYSDATE, 'DD/MM/YY')
            GROUP BY TO_CHAR (nr_of_sundays   1, 'fm00'), TO_CHAR(mydate, 'YYYY')
            ORDER BY TO_CHAR(mydate, 'YYYY'), TO_CHAR (nr_of_sundays   1, 'fm00'); 

year    month      week_num
2022    SEPTEMBER   38
2022    SEPTEMBER   39
2022    OCTOBER  , SEPTEMBER    40
2022    OCTOBER     41
2022    OCTOBER     42
2022    OCTOBER     43
2022    OCTOBER     44
2022    NOVEMBER , OCTOBER      45
2022    NOVEMBER    46
2022    NOVEMBER    47
2022    NOVEMBER    48
2022    DECEMBER , NOVEMBER     49
2022    DECEMBER    50
2022    DECEMBER    51
2022    DECEMBER    52
2022    DECEMBER    53
2023    JANUARY     01
2023    JANUARY     02
...

uj5u.com熱心網友回復：

如果您想要 ISO 周（從星期一開始，在星期日結束，并且屬于包含其大部分日子的任何一年），那么：

SELECT CASE
       WHEN EXTRACT(MONTH FROM week_start)
            = EXTRACT(MONTH FROM week_end)
       THEN TO_CHAR(week_start, 'fmMONTH')
       ELSE TO_CHAR(week_start, 'fmMONTH')
            || ', ' || TO_CHAR(week_end, 'fmMONTH')
       END as month,
       TO_CHAR(week_start, 'IW') AS iso_week,
       TO_CHAR(week_start, 'IYYY') AS iso_year
FROM   (
  SELECT TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW')   (LEVEL - 1) * 7 AS week_start,
         TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW')   LEVEL * 7 - 1 AS week_end
  FROM   DUAL
  CONNECT BY TRUNC(ADD_MONTHS(SYSDATE, -12), 'IW')   (LEVEL - 1) * 7 <= TRUNC(SYSDATE)
);

哪個輸出：

小提琴

標籤：sql 甲骨文

上一篇：帶組的圓線圖

下一篇：Oracle將表列轉換為兩列