設為首頁 加入收藏
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置： 程式碼之家 >> 數據庫知識 >  >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle SQL 生成月曆報表完整實作

     編輯：Oracle 資料庫基礎

Oracle SQL 生成月曆報表完整實作

純 SQL 生成月曆矩陣是報表開發經典需求。本文從基礎日期序列生成、週數計算、行列轉換到最終統計匯總，逐步建構完整月曆查詢。

核心技術點

  • CONNECT BY LEVEL 生成日期序列（遞迴查詢）
  • LAST_DAY 取得月份最後一天
  • TO_CHAR(date, 'fmt') 日期格式化抽取年/月/日/星期/週數
  • DECODE/CASE 行列轉換（樞紐）
  • GROUP BY 週維度匯總

逐步建構過程

1. 取得月份起迄與天數

-- 起始日
SELECT TO_DATE('20061101','YYYYMMDD') AS start_day FROM dual;

-- 結束日
SELECT LAST_DAY(TO_DATE('20061101','YYYYMMDD')) AS end_day FROM dual;

-- 天數
SELECT LAST_DAY(TO_DATE('20061101','YYYYMMDD')) - TO_DATE('20061101','YYYYMMDD') + 1 AS day_cnt FROM dual;

2. 生成該月每一天明細

3. 擴充日期屬性（年、月、日、星期、週數）


SELECT every_day,
       TO_CHAR(every_day,'YYYY') AS year,
       TO_CHAR(every_day,'MM')   AS month,
       TO_CHAR(every_day,'DD')   AS day,
       TO_CHAR(every_day,'DY')   AS weekday,
       LPAD(TO_CHAR(every_day,'W'),6)  AS week_of_month,
       LPAD(TO_CHAR(every_day,'WW'),6) AS week_of_year
FROM   (SELECT TO_DATE('20061101','YYYYMMDD') + LEVEL - 1 AS every_day
        FROM   dual
        CONNECT BY LEVEL <= LAST_DAY(TO_DATE('20061101','YYYYMMDD')) - TO_DATE('20061101','YYYYMMDD') + 1);

4. 行列轉換：每日轉為週矩陣（七欄：日~六）


SELECT every_day,
       TO_CHAR(every_day,'YYYY') AS year,
       TO_CHAR(every_day,'MM')   AS month,
       TO_CHAR(every_day,'DD')   AS day,
       TO_CHAR(every_day,'DY')   AS weekday,
       LPAD(TO_CHAR(every_day,'W'),6)  AS week_of_month,
       LPAD(TO_CHAR(every_day,'WW'),6) AS week_of_year,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期日',TO_CHAR(every_day,'DD')),3) AS sun,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期一',TO_CHAR(every_day,'DD')),3) AS mon,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期二',TO_CHAR(every_day,'DD')),3) AS tue,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期三',TO_CHAR(every_day,'DD')),3) AS wed,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期四',TO_CHAR(every_day,'DD')),3) AS thu,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期五',TO_CHAR(every_day,'DD')),3) AS fri,
       LPAD(DECODE(TO_CHAR(every_day,'DY'),'星期六',TO_CHAR(every_day,'DD')),3) AS sat
FROM   (SELECT TO_DATE('20061101','YYYYMMDD') + LEVEL - 1 AS every_day
        FROM   dual
        CONNECT BY LEVEL <= LAST_DAY(TO_DATE('20061101','YYYYMMDD')) - TO_DATE('20061101','YYYYMMDD') + 1);

5. 週維度統計匯總（最終月曆輸出）


SELECT TO_CHAR(every_day,'W') AS week,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期日',TO_CHAR(every_day,'DD'))) AS sun,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期一',TO_CHAR(every_day,'DD'))) AS mon,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期二',TO_CHAR(every_day,'DD'))) AS tue,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期三',TO_CHAR(every_day,'DD'))) AS wed,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期四',TO_CHAR(every_day,'DD'))) AS thu,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期五',TO_CHAR(every_day,'DD'))) AS fri,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期六',TO_CHAR(every_day,'DD'))) AS sat
FROM   (SELECT TO_DATE('20061101','YYYYMMDD') + LEVEL - 1 AS every_day
        FROM   dual
        CONNECT BY LEVEL <= LAST_DAY(TO_DATE('20061101','YYYYMMDD')) - TO_DATE('20061101','YYYYMMDD') + 1)
GROUP  BY TO_CHAR(every_day,'W')
ORDER  BY TO_CHAR(every_day,'W');

週數計算深度解析

Oracle 提供三種週數格式，語意不同：

格式含義適用場景
W月內週數（1 日至 7 日為第 1 週）月曆顯示
WW年內週數（1 月 1 日所在週為第 1 週）年度報表
IWISO 週數（ISO 8601 標準，週一為週首）國際標準報表

修正月曆週數邏輯（以週日/週一為週首）


SELECT CEIL((TO_CHAR(every_day,'DD') + (TO_CHAR(TO_DATE('20061101','YYYYMMDD'),'D') - 1)) / 7) AS week,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期日',TO_CHAR(every_day,'DD'))) AS sun,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期一',TO_CHAR(every_day,'DD'))) AS mon,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期二',TO_CHAR(every_day,'DD'))) AS tue,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期三',TO_CHAR(every_day,'DD'))) AS wed,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期四',TO_CHAR(every_day,'DD'))) AS thu,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期五',TO_CHAR(every_day,'DD'))) AS fri,
       SUM(DECODE(TO_CHAR(every_day,'DY'),'星期六',TO_CHAR(every_day,'DD'))) AS sat
FROM   (SELECT TO_DATE('20061101','YYYYMMDD') + LEVEL - 1 AS every_day
        FROM   dual
        CONNECT BY LEVEL <= LAST_DAY(TO_DATE('20061101','YYYYMMDD')) - TO_DATE('20061101','YYYYMMDD') + 1)
GROUP  BY CEIL((TO_CHAR(every_day,'DD') + (TO_CHAR(TO_DATE('20061101','YYYYMMDD'),'D') - 1)) / 7)
ORDER  BY week;

進階應用：年曆生成

  • 外層迴圈 12 個月份，內層套用月曆邏輯
  • 使用 PARTITION BY month 進行分組匯總
  • 考慮閏年 2 月 29 日處理

效能與維護建議

  1. 將固定日期參數化（:p_year, :p_month）
  2. 大量報表考慮物化視圖預計算
  3. NLS 設定影響 DY 顯示語言，建議在 Session 設定 NLS_DATE_LANGUAGE
  4. 12c+ 可使用 LATERAL / CROSS APPLY 簡化結構
  1. 上一篇文章：
  2. 下一篇文章：
Oracle 資料庫基礎
Oracle 觸發器開發速查：變量、NEW/OLD、流程控制、常用函數對照表 Oracle 觸發器開發速查：變量、NEW/OLD、流程控制、常用函數對照表

核心語法要點 項目Oracle 語法說明 變量聲明DECL
Oracle Redo Log Buffer 調優關鍵：1% 門檻法則實戰指南 Oracle Redo Log Buffer 調優關鍵：1% 門檻法則實戰指南

前言 Redo Log Buffer 是 Oracle 例項
Oracle Exadata 五大核心技術深度解析：智能緩存、Smart Scan、存儲索引與混合列壓縮 Oracle Exadata 五大核心技術深度解析：智能緩存、Smart Scan、存儲索引與混合列壓縮

架構演進：V1 (HP) → V2 (Sun/Oracle)
Oracle 11g 新特性搶先看：降低變更風險、強化高可用與自動化運維 Oracle 11g 新特性搶先看：降低變更風險、強化高可用與自動化運維

背景與發布時程 隨著 Oracle OpenWorld 大會
Oracle 列遷移與列鏈結根因分析、偵測腳本與修復標準流程 Oracle 11g R1 Data Pump 增強特性全覽 Oracle 分散式交易 MTS/COM+ 整合配置指南：XA 視圖建立、權限授予與登錄表設定全流程 Oracle 資料庫備份與恢復策略深度解析：從核心原則到自動化實踐 VC++ 使用 ADO 連接 Oracle 與 SQL Server 資料庫範例 Oracle SUBSTR 字元數 vs 位元組數陷阱：LENGTH/LENGTHB 差異導致緩衝區不足錯誤解析 Oracle CPU 使用率診斷與優化：從系統層面到 SQL 解析優化
欄目導航
  • Oracle 資料庫相關知識
  • Oracle 資料庫基礎
  • Oracle 教學
    • Copyright © 程式碼之家 All Rights Reserved