2025年07月02日玄貓（BlackCat）

資料函式庫查詢特定星期幾日期

本文提供在不同資料函式庫系統（DB2、Oracle、PostgreSQL、MySQL 和 SQL Server）中查詢特定年份或月份中特定星期幾日期的 SQL 解決方案。文章詳細說明瞭如何使用遞迴查詢、日期函式和條件篩選來實作此目標，並針對每個資料函式庫系統的語法和函式差異進行了詳細解析和程式碼範例解說。

資料函式庫 SQL

SQL查詢日期函式遞迴查詢 DB2 Oracle PostgreSQL MySQL SQL Server

在資料函式庫應用中，經常需要查詢特定年份或月份中特定星期幾的日期。實作此功能需要結合遞迴查詢、日期函式和條件篩選。不同資料函式庫系統的語法和函式略有不同，需要根據實際情況選擇合適的方案。本文提供的 SQL 程式碼片段涵蓋了 DB2、Oracle、PostgreSQL、MySQL 和 SQL Server 等常見資料函式庫系統，可以根據需求修改並應用於實際專案中。對於需要處理大量日期資料的應用，這些技巧可以有效提升查詢效率和程式碼可讀性。

如何找出某年中某個特定星期幾的所有日期

問題描述

本章節旨在解決如何找出某年中所有對應到特定星期幾的日期。例如，您可能想要生成當前年份的所有星期五的列表。

解決方案

無論使用哪種資料函式庫廠商，解決方案的關鍵在於傳回當前年份的每一天，並保留對應到您關心的星期幾的日期。以下範例將保留所有星期五的日期。

DB2 解決方案

使用遞迴 WITH 子句傳回當前年份的每一天，然後使用 DAYNAME 函式保留星期五：

WITH X (dy, yr) AS (
  SELECT dy, YEAR(dy) yr
  FROM (
    SELECT (CURRENT_DATE - DAYOFYEAR(CURRENT_DATE) DAYS + 1 DAYS) AS dy
    FROM t1
  ) tmp1
  UNION ALL
  SELECT dy + 1 DAYS, yr
  FROM X
  WHERE YEAR(dy + 1 DAY) = yr
)
SELECT dy
FROM X
WHERE DAYNAME(dy) = 'Friday';

Oracle 解決方案

使用遞迴 CONNECT BY 子句傳回當前年份的每一天，然後使用 TO_CHAR 函式保留星期五：

WITH X AS (
  SELECT TRUNC(SYSDATE, 'y') + LEVEL - 1 dy
  FROM t1
  CONNECT BY LEVEL <= ADD_MONTHS(TRUNC(SYSDATE, 'y'), 12) - TRUNC(SYSDATE, 'y')
)
SELECT *
FROM X
WHERE TO_CHAR(dy, 'dy') = 'fri';

PostgreSQL 解決方案

使用遞迴 CTE 生成每年的每一天，並過濾掉不是星期五的日期。這個版本使用了 ANSI 標準的 EXTRACT，因此可以在多種 RDBMS 上執行：

WITH RECURSIVE cal (dy) AS (
  SELECT CURRENT_DATE - (CAST(EXTRACT(DOY FROM CURRENT_DATE) AS INTEGER) - 1)
  UNION ALL
  SELECT dy + 1
  FROM cal
  WHERE EXTRACT(YEAR FROM dy) = EXTRACT(YEAR FROM (dy + 1))
)
SELECT dy, EXTRACT(DOW FROM dy)
FROM cal
WHERE CAST(EXTRACT(DOW FROM dy) AS INTEGER) = 5; -- 注意：PostgreSQL中星期五是5（0代表星期日）

MySQL 解決方案

使用遞迴 CTE 找到一年中的所有日期，然後過濾掉不是星期五的日期：

WITH RECURSIVE cal (dy, yr) AS (
  SELECT dy, EXTRACT(YEAR FROM dy) AS yr
  FROM (
    SELECT ADDDATE(ADDDATE(CURRENT_DATE, INTERVAL -DAYOFYEAR(CURRENT_DATE) DAY), INTERVAL 1 DAY) AS dy
  ) AS tmp1
  UNION ALL
  SELECT DATE_ADD(dy, INTERVAL 1 DAY), yr
  FROM cal
  WHERE EXTRACT(YEAR FROM DATE_ADD(dy, INTERVAL 1 DAY)) = yr
)
SELECT dy
FROM cal
WHERE DAYOFWEEK(dy) = 6; -- 注意：MySQL中星期五是6（1代表星期日）

SQL Server 解決方案

使用遞迴 WITH 子句傳回當前年份的每一天，然後使用 DATENAME 函式保留星期五：

WITH X (dy, yr) AS (
  SELECT dy, YEAR(dy) yr
  FROM (
    SELECT GETDATE() - DATEPART(dy, GETDATE()) + 1 dy
    FROM t1
  ) tmp1
  UNION ALL
  SELECT DATEADD(dd, 1, dy), yr
  FROM X
  WHERE YEAR(DATEADD(dd, 1, dy)) = yr
)
SELECT x.dy
FROM X
WHERE DATENAME(dw, x.dy) = 'Friday'
OPTION (MAXRECURSION 400);

詳細解析

DB2 解析

要找出當前年份的所有星期五，首先需要傳回當前年份的每一天。第一步是使用 DAYOFYEAR 函式找到當年的第一天。從當前日期中減去 DAYOFYEAR(CURRENT_DATE) 的值，得到前一年的最後一天，然後加一得到當年的第一天。

SELECT (CURRENT_DATE - DAYOFYEAR(CURRENT_DATE) DAYS + 1 DAYS) AS dy
FROM t1;

接著，使用 WITH 子句重複地在當年的第一天上加一天，直到不再是當年為止。這樣就會得到當年的每一天。

最後，使用 DAYNAME 函式保留星期五的日期。

Oracle 解析

同樣地，首先使用 TRUNC 函式找到當年的第一天。

SELECT TRUNC(SYSDATE, 'y') dy
FROM t1;

然後，使用 CONNECT BY 子句傳回當年的每一天。

最後，使用 TO_CHAR 函式保留星期五的日期。

PostgreSQL、MySQL 和 SQL Server 解析

這些資料函式庫的解析與 DB2 和 Oracle 相似，都是先找到當年的第一天，然後使用遞迴 CTE 或 WITH 子句生成當年的每一天，最後根據各自的函式（如 EXTRACT、DAYOFWEEK、DATENAME）保留星期五的日期。

重點整理

使用遞迴查詢（如 WITH 子句或 CONNECT BY 子句）生成一年中的每一天。
使用特定資料函式庫的日期函式（如 DAYNAME、TO_CHAR、EXTRACT、DAYOFWEEK、DATENAME）來判斷某天是星期幾。
篩選出特定的星期幾（如星期五）的日期。

程式碼重點

遞迴查詢：用於生成一年中的每一天。
日期函式：用於判斷某天的星期幾。
篩選條件：用於保留特定的星期幾。

程式碼範例重點解說

每個範例程式碼都展示瞭如何使用遞迴查詢和日期函式來找出特定年份中某個星期幾的所有日期。無論是 DB2、Oracle、PostgreSQL、MySQL 或 SQL Server，核心思路都是相同的：生成一年中的每一天並篩選出目標星期幾。不同的是，各資料函式庫系統使用的語法和函式有所不同。

例如，在 DB2 中，使用 DAYNAME 函式來判斷某天是星期幾，而在 Oracle 中，則使用 TO_CHAR 函式來達到同樣的效果。

日期操作：找出特定星期幾在某月的第一個和最後一個日期

在處理日期相關的問題時，經常需要找出某個特定星期幾（如星期一）在某個月份中的第一個和最後一個日期。本文將介紹如何使用不同的資料函式倉管理系統（DBMS）來解決這個問題。

問題描述

給定一個月份，需要找出該月份中某個特定星期幾的第一個和最後一個日期。例如，找出當前月份的第一個和最後一個星期一。

解決方案

不同的DBMS提供了不同的函式和語法來處理日期。以下是使用不同DBMS的解決方案：

DB2

DB2使用遞迴的WITH子句來產生當前月份的每一天，並使用CASE運算式來標記所有的星期一。第一個和最後一個星期一將是被標記日期中的最早和最晚日期。

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) DAY + 1 DAY) 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) first_monday, MAX(dy) last_monday
FROM X
WHERE is_monday = 1;

Oracle

Oracle使用NEXT_DAY和LAST_DAY函式，結合聰明的日期運算，來找出當前月份的第一個和最後一個星期一。

SELECT NEXT_DAY(TRUNC(SYSDATE, 'mm') - 1, 'MONDAY') first_monday,
       NEXT_DAY(LAST_DAY(TRUNC(SYSDATE, 'mm')) - 7, 'MONDAY') last_monday
FROM dual;

PostgreSQL

PostgreSQL使用DATE_TRUNC函式來找出月份的第一天。然後，使用簡單的算術運算來找出當前月份的第一個和最後一個星期一。

SELECT first_monday,
       CASE TO_CHAR(first_monday + 28, 'mm')
         WHEN mth THEN first_monday + 28
         ELSE first_monday + 21
       END AS last_monday
FROM (
  SELECT CASE SIGN(CAST(TO_CHAR(dy, 'd') AS INTEGER) - 2)
           WHEN 0 THEN dy
           WHEN -1 THEN dy + ABS(CAST(TO_CHAR(dy, 'd') AS INTEGER) - 2)
           WHEN 1 THEN (7 - (CAST(TO_CHAR(dy, 'd') AS INTEGER) - 2)) + dy
         END AS first_monday,
         mth
  FROM (
    SELECT CAST(DATE_TRUNC('month', CURRENT_DATE) AS DATE) AS dy,
           TO_CHAR(CURRENT_DATE, 'mm') AS mth
    FROM t1
  ) x
) y;

MySQL

MySQL使用ADDDATE函式來找出月份的第一天。然後，使用簡單的算術運算來找出當前月份的第一個和最後一個星期一。

SELECT first_monday,
       CASE MONTH(ADDDATE(first_monday, 28))
         WHEN mth THEN ADDDATE(first_monday, 28)
         ELSE ADDDATE(first_monday, 21)
       END last_monday
FROM (
  SELECT CASE SIGN(DAYOFWEEK(dy) - 2)
           WHEN 0 THEN dy
           WHEN -1 THEN ADDDATE(dy, ABS(DAYOFWEEK(dy) - 2))
           WHEN 1 THEN ADDDATE(dy, (7 - (DAYOFWEEK(dy) - 2)))
         END first_monday,
         mth
  FROM (
    SELECT ADDDATE(ADDDATE(CURRENT_DATE, -DAY(CURRENT_DATE)), 1) dy,
           MONTH(CURRENT_DATE) mth
    FROM t1
  ) x
) y;

SQL Server

SQL Server使用遞迴的WITH子句來產生當前月份的每一天，並使用CASE運算式來標記所有的星期一。第一個和最後一個星期一將是被標記日期中的最早和最晚日期。

WITH X (dy, mth, is_monday) 
AS (
  SELECT dy, mth,
         CASE WHEN DATEPART(dw, dy) = 2 THEN 1 ELSE 0 END
  FROM (
    SELECT DATEADD(day, 1, DATEADD(day, -DAY(GETDATE()), GETDATE())) dy,
           MONTH(GETDATE()) mth
    FROM t1
  ) tmp1
  UNION ALL
  SELECT DATEADD(day, 1, dy),
         mth,
         CASE WHEN DATEPART(dw, DATEADD(day, 1, dy)) = 2 THEN 1 ELSE 0 END
  FROM X
  WHERE MONTH(DATEADD(day, 1, dy)) = mth
)
SELECT MIN(dy) first_monday, MAX(dy) last_monday
FROM X
WHERE is_monday = 1;

程式碼解析

DB2

WITH X (dy, mth, is_monday) 
AS (
  SELECT dy, MONTH(dy),
         CASE WHEN DAYNAME(dy) = 'Monday' THEN 1 ELSE 0 END

#### 程式碼解密：
- 使用遞迴CTE（Common Table Expression）來生成當前月份的所有日期。
- `DAYNAME(dy)` 用於取得日期對應的星期名稱。
- `CASE` 陳述式用於判斷日期是否為星期一，如果是則標記為1，否則為0。

#### Oracle

```sql
SELECT NEXT_DAY(TRUNC(SYSDATE, 'mm') - 1, 'MONDAY') first_monday,
       NEXT_DAY(LAST_DAY(TRUNC(SYSDATE, 'mm')) - 7, 'MONDAY') last_monday

#### 程式碼解密：
- `TRUNC(SYSDATE, 'mm')` 將當前日期截斷到月份的第一天。
- `NEXT_DAY` 用於找到下一個指定的星期幾（這裡是星期一）。
- `LAST_DAY` 用於找到當前月份的最後一天。

#### PostgreSQL

```sql
SELECT first_monday,
       CASE TO_CHAR(first_monday + 28, 'mm')
         WHEN mth THEN first_monday + 28
         ELSE first_monday + 21
       END AS last_monday

#### 程式碼解密：
- 使用 `DATE_TRUNC` 將當前日期截斷到月份的第一天。
- `TO_CHAR` 用於將日期轉換為字串，以取得月份。
- `CASE` 陳述式用於判斷加上28天后是否仍在同一月份，以此決定最後一個星期一的日期。

#### MySQL

```sql
SELECT first_monday,
       CASE MONTH(ADDDATE(first_monday, 28))
         WHEN mth THEN ADDDATE(first_monday, 28)
         ELSE ADDDATE(first_monday, 21)
       END last_monday

#### 程式碼解密：
- 使用 `ADDDATE` 將日期加上指定的天數，以計算第一個和最後一個星期一。
- `MONTH` 用於取得日期的月份，以進行比較。

#### SQL Server

```sql
WITH X (dy, mth, is_monday) 
AS (
  SELECT dy, mth,
         CASE WHEN DATEPART(dw, dy) = 2 THEN 1 ELSE 0 END

#### 程式碼解密：
- 使用遞迴CTE生成當前月份的所有日期。
- `DATEPART(dw, dy)` 用於取得日期對應的星期幾（dw代表一週中的第幾天）。
- `CASE` 陳述式用於判斷日期是否為星期一（假設星期一對應的值是2）。