如何在Oracle中创建日历表 [英] How to create Calendar Table in Oracle

问题描述

如何创建日历表Oracle.就像我有假期表

How to create Calendar Table Oracle. like i have table of holidays

Id  FromDate    ToDate  Description
1   1-Feb-11    3-Feb-11    Maintance
3   2-Sep-09    5-Sep-09    Eid Holiday Fine Block
4   3-Dec-09    4-Dec-09    Due to System Problem
5   4-Dec-07    04-Dec-07   National Day

我要从此表中的CTE设为最小fromdate和最大todate.

I want to make CTE from this table from minimum fromdate and maximum todate.

像这样的期望表.

Date        HolidayName

11-Feb-11   Maintance
12-Feb-11   Maintance
13-Feb-11   Maintance
02-Sep-09   Eid Holiday
03-Sep-09   Eid Holiday
04-Sep-09   Eid Holiday
05-Sep-09   Eid Holiday

推荐答案

connect by level子句可以使用:

 select FromDate+level-1 "Date", Description "HolidayName"
   from holiday 
connect by level <=  ToDate - FromDate + 1
  group by FromDate+level-1, Description
  order by 1;

演示

这篇关于如何在Oracle中创建日历表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！