SQL查詢特定月份第一個和最後一個星期一

在資料函式庫應用中，經常需要處理日期相關的查詢。本文將介紹如何使用 SQL 語法查詢特定月份的第一個和最後一個星期一日期。由於不同資料函式庫系統的日期函式和語法存在差異，我們將針對 Oracle、PostgreSQL、MySQL 和 DB2 分別提供解決方案，並比較它們之間的異同。這些方法的核心概念都是先取得月份的第一天和最後一天，再利用內建函式或日期運算找出對應的星期一日期。

尋找特定月份的第一個和最後一個星期一的日期

本章節主要討論如何使用SQL查詢來找出特定月份（本例中為當前月份）的第一個和最後一個星期一的日期。不同的資料函式倉管理系統（DBMS）如Oracle、PostgreSQL、MySQL和DB2有不同的實作方法。

Oracle解決方案

Oracle提供了NEXT_DAY函式，使得這個問題變得簡單。以下是步驟：

找到當前月份的第一天：使用TRUNC函式。
```
select trunc(sysdate,'mm') dy from dual;
```
找到當前月份的第一個星期一：先找到上個月的最後一天，然後使用NEXT_DAY函式。
```
select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday from dual;
```
找到當前月份的最後一個星期一：先找到當前月份的最後一天，然後倒退7天，再使用NEXT_DAY函式。
```
select next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday from dual;
```

程式碼範例：

-- 查詢當前月份的第一個星期一
select next_day(trunc(sysdate,'mm')-1,'MONDAY') first_monday from dual;

-- 查詢當前月份的最後一個星期一
select next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY') last_monday from dual;

內容解密：

TRUNC(sysdate,'mm') 將當前日期截斷到月份的第一天。
NEXT_DAY 函式用於找出下一個指定的星期幾。
LAST_DAY 函式用於找出當前月份的最後一天。

PostgreSQL和MySQL解決方案

這兩個DBMS的解決方案相似，主要透過計算來實作。

找到當前月份的第一天。
計算第一個星期一的日期：透過比較第一天的星期與星期一的差值來計算。
計算最後一個星期一的日期：類別似地，先找到最後一天，然後倒推到最近的星期一。

PostgreSQL 程式碼範例：

-- PostgreSQL查詢第一個和最後一個星期一
SELECT 
    date_trunc('month', current_date)::date AS first_day,
    date_trunc('month', current_date)::date + 
    (CASE 
        WHEN EXTRACT(DOW FROM date_trunc('month', current_date)::date) = 1 THEN 0
        ELSE (8 - EXTRACT(DOW FROM date_trunc('month', current_date)::date)) % 7
    END) AS first_monday,
    (date_trunc('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day')::date - 
    (CASE 
        WHEN EXTRACT(DOW FROM (date_trunc('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day')::date) = 1 THEN 0
        ELSE (EXTRACT(DOW FROM (date_trunc('month', current_date) + INTERVAL '1 month' - INTERVAL '1 day')::date) - 1)
    END) AS last_monday;

MySQL 程式碼範例：

-- MySQL查詢第一個和最後一個星期一
SELECT 
    DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day,
    DATE_FORMAT(CURDATE(), '%Y-%m-01') + INTERVAL ((8 - DAYOFWEEK(DATE_FORMAT(CURDATE(), '%Y-%m-01')) + 1) % 7) DAY AS first_monday,
    LAST_DAY(CURDATE()) - INTERVAL ((DAYOFWEEK(LAST_DAY(CURDATE())) + 6) % 7) DAY AS last_monday;

內容解密：

PostgreSQL使用date_trunc來找到月份的第一天，並使用EXTRACT(DOW FROM date)來取得星期的數字表示（0代表星期日，1代表星期一，依此類別推）。
MySQL使用DATE_FORMAT來格式化日期，並使用DAYOFWEEK來取得星期的數字表示（1代表星期日，2代表星期一，依此類別推）。
這兩個查詢都利用了日期計算和條件表示式來找出第一個和最後一個星期一。

DB2解決方案

DB2可以使用遞迴CTE（Common Table Expression）來解決這個問題。

程式碼範例：

WITH X (dy, mth, is_monday) AS (
    SELECT dy, MONTH(dy), CASE WHEN DAYNAME(dy) = 'Monday' THEN 1 ELSE 0 END
    FROM (
        SELECT (CURRENT_DATE - DAY(CURRENT_DATE) DAYS + 1 DAY) AS dy
        FROM T1
    ) TMP1
    UNION ALL
    SELECT (dy + 1 DAY), mth, CASE WHEN DAYNAME(dy + 1 DAY) = 'Monday' THEN 1 ELSE 0 END
    FROM X
    WHERE MONTH(dy + 1 DAY) = mth
)
SELECT MIN(dy) AS first_monday, MAX(dy) AS last_monday
FROM X
WHERE is_monday = 1;

內容解密：

使用遞迴CTE X 從月份的第一天開始，每天遞增，直到超出該月份。
使用CASE表示式檢查每天是否是星期一，如果是，則標記為1，否則為0。
最後，查詢 X 中 is_monday = 1 的最小和最大日期，分別代表第一個和最後一個星期一。

製作月曆

本章節將介紹如何使用 SQL 製作當前月份的月曆。月曆的格式將類別似於桌面上的月曆，具有七列，通常為五行。

問題描述

你希望製作當前月份的月曆。月曆應該格式化為類別似於你可能放在桌子上的月曆：七列橫跨，通常五行向下。

解決方案

每個解決方案看起來可能略有不同，但它們都以相同的方式解決問題：傳回當前月份的每一天，然後根據每週的星期幾進行樞轉，以製作月曆。

DB2 解決方案

使用遞迴 WITH 子句傳回當前月份的每一天。然後使用 CASE 和 MAX 根據星期幾進行樞轉：

WITH X(DY, DM, MTH, DW, WK) AS (
  SELECT (CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) DY,
         DAY((CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY)) DM,
         MONTH(CURRENT_DATE) MTH,
         DAYOFWEEK(CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) DW,
         WEEK_ISO(CURRENT_DATE - DAY(CURRENT_DATE) DAY + 1 DAY) WK
  FROM T1
  UNION ALL
  SELECT DY + 1 DAY, DAY(DY + 1 DAY), MTH,
         DAYOFWEEK(DY + 1 DAY), WEEK_ISO(DY + 1 DAY)
  FROM X
  WHERE MONTH(DY + 1 DAY) = MTH
)
SELECT MAX(CASE DW WHEN 2 THEN DM END) AS MO,
       MAX(CASE DW WHEN 3 THEN DM END) AS TU,
       MAX(CASE DW WHEN 4 THEN DM END) AS WE,
       MAX(CASE DW WHEN 5 THEN DM END) AS TH,
       MAX(CASE DW WHEN 6 THEN DM END) AS FR,
       MAX(CASE DW WHEN 7 THEN DM END) AS SA,
       MAX(CASE DW WHEN 1 THEN DM END) AS SU
FROM X
GROUP BY WK
ORDER BY WK;

程式碼解密：

使用遞迴 WITH 子句建立臨時表 X，包含日期（DY）、日期中的天數（DM）、月份（MTH）、星期幾（DW）和週數（WK）。
第一個 SELECT 陳述式取得當前月份的第一天及其相關資訊。
UNION ALL 之後的 SELECT 陳述式遞迴地增加日期，直到下個月的第一天，篩選出屬於當前月份的日期。
最外層的 SELECT 陳述式使用 CASE 和 MAX 聚合函式，根據星期幾將日期樞轉到對應的列中。
結果按週數（WK）分組並排序。

Oracle 解決方案

使用遞迴 CONNECT BY 子句傳回當前月份的每一天。然後使用 CASE 和 MAX 根據星期幾進行樞轉：

WITH X AS (
  SELECT *
  FROM (
    SELECT TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'IW') WK,
           TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'DD') DM,
           TO_NUMBER(TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'D')) DW,
           TO_CHAR(TRUNC(SYSDATE, 'MM') + LEVEL - 1, 'MM') CURR_MTH,
           TO_CHAR(SYSDATE, 'MM') MTH
    FROM DUAL
    CONNECT BY LEVEL <= 31
  )
  WHERE CURR_MTH = MTH
)
SELECT MAX(CASE DW WHEN 2 THEN DM END) MO,
       MAX(CASE DW WHEN 3 THEN DM END) TU,
       MAX(CASE DW WHEN 4 THEN DM END) WE,
       MAX(CASE DW WHEN 5 THEN DM END) TH,
       MAX(CASE DW WHEN 6 THEN DM END) FR,
       MAX(CASE DW WHEN 7 THEN DM END) SA,
       MAX(CASE DW WHEN 1 THEN DM END) SU
FROM X
GROUP BY WK
ORDER BY WK;

程式碼解密：

使用遞迴 CONNECT BY 子句建立臨時表 X，包含週數（WK）、日期中的天數（DM）、星期幾（DW）和月份資訊。
第一個 SELECT 陳述式生成從當前月份第一天開始的31天日期序列。
篩選出屬於當前月份的日期。
最外層的 SELECT 陳述式使用 CASE 和 MAX 聚合函式，根據星期幾將日期樞轉到對應的列中。
結果按週數（WK）分組並排序。

PostgreSQL 解決方案

使用 GENERATE_SERIES 函式傳回當前月份的每一天。然後使用 MAX 和 CASE 根據星期幾進行樞轉：

SELECT MAX(CASE DW WHEN 2 THEN DM END) AS MO,
       MAX(CASE DW WHEN 3 THEN DM END) AS TU,
       MAX(CASE DW WHEN 4 THEN DM END) AS WE,
       MAX(CASE DW WHEN 5 THEN DM END) AS TH,
       MAX(CASE DW WHEN 6 THEN DM END) AS FR,
       MAX(CASE DW WHEN 7 THEN DM END) AS SA,
       MAX(CASE DW WHEN 1 THEN DM END) AS SU
FROM (
  SELECT *
  FROM (
    SELECT CAST(DATE_TRUNC('MONTH', CURRENT_DATE) AS DATE) + X.ID,
           TO_CHAR(CAST(DATE_TRUNC('MONTH', CURRENT_DATE) AS DATE) + X.ID, 'IW') AS WK,
           TO_CHAR(CAST(DATE_TRUNC('MONTH', CURRENT_DATE) AS DATE) + X.ID, 'DD') AS DM,
           CAST(TO_CHAR(CAST(DATE_TRUNC('MONTH', CURRENT_DATE) AS DATE) + X.ID, 'D') AS INTEGER) AS DW,
           TO_CHAR(CAST(DATE_TRUNC('MONTH', CURRENT_DATE) AS DATE) + X.ID, 'MM') AS CURR_MTH,
           TO_CHAR(CURRENT_DATE, 'MM') AS MTH
    FROM GENERATE_SERIES(0, 31) X(ID)
  ) X
  WHERE MTH = CURR_MTH
) Y
GROUP BY WK
ORDER BY WK;

程式碼解密：

使用 GENERATE_SERIES 函式生成從0到31的序列，用於與當前月份第一天相加，得到當前月份的每一天。
使用 DATE_TRUNC 函式截斷到月份第一天，並與序列相加，得到每一天的日期、週數、日期中的天數、星期幾等資訊。
篩選出屬於當前月份的日期。
最外層的 SELECT 陳述式使用 CASE 和 MAX 聚合函式，根據星期幾將日期樞轉到對應的列中。
結果按週數（WK）分組並排序。

MySQL 解決方案

使用遞迴 CTE 傳回當前月份的每一天。然後使用 MAX 和 CASE 根據星期幾進行樞轉：

WITH RECURSIVE X(DY, DM, MTH, DW, WK) AS (
  SELECT DY,
         DAY(DY) DM,
         DATEPART(M, DY) MTH,
         DATEPART(DW, DY) DW,
         CASE WHEN DATEPART(DW, DY) = 1 THEN DATEPART(WW, DY) - 1 ELSE DATEPART(WW, DY) END WK
  FROM (
    SELECT DATE_ADD(DAY, -DAY(GETDATE()) + 1, GETDATE()) DY
    FROM T1
  ) X
  UNION ALL
  SELECT DATEADD(D, 1, DY), DAY(DATE_ADD(D, 1, DY)), MTH,
         DATEPART(DW, DATEADD(D, 1, DY)),
         CASE WHEN DATEPART(DW, DATE_ADD(D, 1, DY)) = 1 THEN DATEPART(WK, DATE_ADD(D, 1, DY)) - 1 ELSE DATEPART(WK, DATE_ADD(D, 1, DY)) END
  FROM X
  WHERE DATEPART(M, DATE_ADD(D, 1, DY)) = MTH
)
SELECT MAX(CASE DW WHEN 2 THEN DM END) AS MO,
       MAX(CASE DW WHEN 3 THEN DM END) AS TU,
       MAX(CASE DW WHEN 4 THEN DM END) AS WE,
       MAX(CASE DW WHEN 5 THEN DM END) AS TH,
       MAX(CASE DW WHEN 6 THEN DM END) AS FR,
       MAX(CASE DW WHEN 7 THEN DM END) AS SA,
       MAX(CASE DW WHEN 1 THEN DM END) AS SU
FROM X
GROUP BY WK
ORDER BY WK;

程式碼解密：

使用遞迴 CTE 建立臨時表 X，包含日期（DY）、日期中的天數（DM）、月份（MTH）、星期幾（DW）和週數（WK）。
第一個 SELECT 陳述式取得當前月份的第一天及其相關資訊。
UNION ALL 之後的 SELECT 陳述式遞迴地增加日期，直到下個月的第一天，篩選出屬於當前月份的日期。
最外層的 SELECT 陳述式使用 CASE 和 MAX 聚合函式，根據星期幾將日期樞轉到對應的列中。
結果按週數（WK）分組並排序。

建立月曆的 SQL 查詢

本篇文章將介紹如何使用 SQL 查詢建立一個月曆。我們將使用遞迴查詢（Recursive Query）和條件聚合（Conditional Aggregation）來實作這一目標。

SQL Server 解決方案

首先，我們使用遞迴通用表表達式（Recursive Common Table Expression，CTE）來產生當前月份的每一天。

WITH x(dy, dm, mth, dw, wk) AS (
    SELECT 
        dateadd(day, -day(getdate()) + 1, getdate()) dy,
        day(dateadd(day, -day(getdate()) + 1, getdate())) dm,
        datepart(m, dateadd(day, -day(getdate()) + 1, getdate())) mth,
        datepart(dw, dateadd(day, -day(getdate()) + 1, getdate())) dw,
        CASE 
            WHEN datepart(dw, dateadd(day, -day(getdate()) + 1, getdate())) = 1 
            THEN datepart(ww, dateadd(day, -day(getdate()) + 1, getdate())) - 1
            ELSE datepart(ww, dateadd(day, -day(getdate()) + 1, getdate()))
        END wk
    FROM t1
    UNION ALL
    SELECT 
        dateadd(d, 1, dy), 
        day(dateadd(d, 1, dy)), 
        mth,
        datepart(dw, dateadd(d, 1, dy)),
        CASE 
            WHEN datepart(dw, dateadd(d, 1, dy)) = 1 
            THEN datepart(wk, dateadd(d, 1, dy)) - 1
            ELSE datepart(wk, dateadd(d, 1, dy))
        END
    FROM x
    WHERE datepart(m, dateadd(d, 1, dy)) = mth
)
SELECT 
    MAX(CASE dw WHEN 2 THEN dm END) AS Mo,
    MAX(CASE dw WHEN 3 THEN dm END) AS Tu,
    MAX(CASE dw WHEN 4 THEN dm END) AS We,
    MAX(CASE dw WHEN 5 THEN dm END) AS Th,
    MAX(CASE dw WHEN 6 THEN dm END) AS Fr,
    MAX(CASE dw WHEN 7 THEN dm END) AS Sa,
    MAX(CASE dw WHEN 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;

程式碼解密：

遞迴 CTE：使用 WITH 子句定義一個遞迴 CTE x，用於產生當前月份的每一天。
初始查詢：第一個 SELECT 陳述式取得當前月份的第一天，並計算相關資訊（日期、月份、星期幾、周數）。
遞迴查詢：UNION ALL 後的 SELECT 陳述式遞迴地增加日期，直到超出當前月份。
條件聚合：外部查詢使用 CASE 和 MAX 聚合函式，將每一天的日期按照星期幾分組顯示。
結果排序：最終結果按照周數排序，呈現一個月曆。

DB2 解決方案

DB2 的解決方案與 SQL Server 類別似，使用遞迴 CTE 和條件聚合。

WITH x(dy, dm, mth, dw, wk) AS (
    SELECT 
        (current_date - day(current_date) DAY + 1 DAY) dy,
        day((current_date - day(current_date) DAY + 1 DAY)) dm,
        month(current_date) mth,
        dayofweek(current_date - day(current_date) DAY + 1 DAY) dw,
        week_iso(current_date - day(current_date) DAY + 1 DAY) wk
    FROM t1
    UNION ALL
    SELECT 
        dy + 1 DAY, 
        day(dy + 1 DAY), 
        mth,
        dayofweek(dy + 1 DAY), 
        week_iso(dy + 1 DAY)
    FROM x
    WHERE month(dy + 1 DAY) = mth
)
SELECT 
    MAX(CASE dw WHEN 2 THEN dm END) AS Mo,
    MAX(CASE dw WHEN 3 THEN dm END) AS Tu,
    MAX(CASE dw WHEN 4 THEN dm END) AS We,
    MAX(CASE dw WHEN 5 THEN dm END) AS Th,
    MAX(CASE dw WHEN 6 THEN dm END) AS Fr,
    MAX(CASE dw WHEN 7 THEN dm END) AS Sa,
    MAX(CASE dw WHEN 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;

程式碼解密：

與 SQL Server 版本類別似，但使用 DB2 特有的日期函式（如 dayofweek 和 week_iso）。

Oracle 解決方案

Oracle 可以使用 CONNECT BY 子句產生遞迴查詢。

WITH x AS (
    SELECT 
        trunc(sysdate, 'mm') + level - 1 dy,
        to_char(trunc(sysdate, 'mm') + level - 1, 'dd') dm,
        to_char(sysdate, 'mm') mth,
        to_number(to_char(trunc(sysdate, 'mm') + level - 1, 'd')) dw,
        to_char(trunc(sysdate, 'mm') + level - 1, 'iw') wk
    FROM dual
    CONNECT BY level <= TO_CHAR(LAST_DAY(SYSDATE), 'DD')
)
SELECT 
    MAX(CASE dw WHEN 2 THEN dm END) AS Mo,
    MAX(CASE dw WHEN 3 THEN dm END) AS Tu,
    MAX(CASE dw WHEN 4 THEN dm END) AS We,
    MAX(CASE dw WHEN 5 THEN dm END) AS Th,
    MAX(CASE dw WHEN 6 THEN dm END) AS Fr,
    MAX(CASE dw WHEN 7 THEN dm END) AS Sa,
    MAX(CASE dw WHEN 1 THEN dm END) AS Su
FROM x
GROUP BY wk
ORDER BY wk;

程式碼解密：

使用 CONNECT BY：利用 CONNECT BY 子句產生從 1 到當前月份最後一天的數字序列。
日期計算：計算每一天的日期、星期幾和周數等資訊。
條件聚合：與前述相同，使用 CASE 和 MAX 將日期按照星期分組。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。