Oracle提供的日期函数可以满足我们日常用到的对日期的所有操作。通常，在设计表结构的时候，我们也常常会遇到日期类型的字段。如果业务里面需要类似汇总给定日期所在周的资料，汇总给定日期所在月的各周资料等操作，那直接写SQL语句将比较复杂。

所以，我是直接在数据里面建立一个日期的主档表，在里面将可能用到的统计字段先计算后，后面业务逻辑的汇总只需连接这个表就可以得到想要的结果。-

下面是这个表的建立和数据的生成过程，可以在里面新加字段以满足不同的需求，相关的索引可以根据自己的需要去建立！

同时欢迎大家一起讨论更好的方法！不足之处，请不吝指教！

1. 建立日期主档表

CREATE TABLE DATE_MAIN

(

DATE_TIME                  DATE,

DAY_OF_WEEK                INTEGER,

WEEK_OF_YEAR               INTEGER,

MONTH_OF_YEAR              INTEGER,

WEEK_OF_MONTH              INTEGER,

YEAR_AND_MONTH             CHAR(7),

FIRST_DAY_OF_WEEK          DATE,

LAST_DAY_OF_WEEK           DATE,

FIRST_DAY_OF_WEEK_OF_MONTH DATE,

LAST_DAY_OF_WEEK_OF_MONTH  DATE

)

2. 插入日期资料

declare

i integer;

begin_date char(10);

begin

begin_date := '2008-01-01';

FOR i IN 0..3650

LOOP

execute IMMEDIATE

'INSERT INTO date_main(Date_Time)

VALUES(:X)' USING to_date(begin_date,'YYYY-MM-DD') + i;

END LOOP;

end;

3. 设置相关字段的值

update DATE_MAIN

SET month_of_year = to_number(to_char(date_time,'MM')),

week_of_year = to_number(to_char(date_time,'IW')),

day_of_week = to_number(to_char(date_time,'D'));

--设置周一为每周的第一天

UPDATE date_main SET day_of_week =day_of_week - 1;

UPDATE date_main SET day_of_week = 7

WHERE day_of_week =0;

4. 设置每月的周别

Declare

current_year CHAR(4);

current_month INT;

current_week INT;

temp_year CHAR(4);

temp_month INT;

weekOfMonth INT;

dayOfWeek INT;

begin

-- Test statements here

current_year := '1999';

current_month := -1;

current_week :=-1;

FOR X IN (

SELECT to_char(date_time,'YYYYMMDD') dat

,month_of_year

,week_of_year

,day_of_week

,week_of_month

FROM date_main s

ORDER BY s.date_time

)

LOOP

temp_year := substr(X.dat,1,4);

IF temp_year <> current_year THEN  --跨年

current_year := temp_year;

current_month := X.month_of_year;

current_week := X.week_of_year;

dayOfWeek := X.day_of_week;

weekOfMonth := 1;

ELSE  --同一年

temp_month := X.month_of_year;

IF temp_month <> current_month THEN --跨月

weekOfMonth := 1;

current_month := temp_month;

ELSE --同月份

IF current_week <> X.week_of_year THEN

current_week := X.week_of_year;

weekOfMonth := weekOfMonth + 1;

END IF;

END IF;

END IF;

EXECUTE IMMEDIATE

'UPDATE date_main '

|| 'SET week_of_month = :A '

|| 'WHERE date_time = to_date(:B,''YYYYMMDD'')' USING weekOfMonth,X.DAT;

END LOOP;

end;

5.设置每周的第一天和最后一天

--设置每周的第一天

UPDATE Date_main

SET first_day_of_week  = trunc(date_time,'DD')-to_char(date_time -1,'D') + 1;

--设置每周的最后一天

UPDATE Date_main

SET last_day_of_week = first_day_of_week + 6;

UPDATE Date_main A

SET (first_day_of_Week_of_Month,LAST_day_of_week_of_Month) = (

SELECT mind,maxd FROM

(

SELECT MIN(date_time) MIND ,MAX(date_time) MAXD,year_and_month,week_of_month

FROM date_main GROUP BY year_and_month,week_of_month

) B

WHERE A.Year_And_Month = B.year_and_month AND A.week_of_month = B.week_of_month

本文出自：亿恩科技【www.enkj.com】