SQL 创建周期数周的表 - 在月末休息 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1836532/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL to create table with weeks in period - having breaks at month's end (Oracle)
提问by hihops
I need to create a table with the following structure:
我需要创建一个具有以下结构的表：
calendar week; week start date; week end date
日历周；周开始日期；周末结束日期
which contains all weeks beginning in 2007 until the current week.
其中包含从 2007 年开始到当前周的所有周。
The special thing is, that when an end of month falls within a week, the week is cut in two slices - one record that has a start date that is the beginning of the week and the end date is the last day of the month, and one record that contains the dates of the rest of the week (start date is first of the new month, end date is last day of the week).
特殊之处在于，当月末落在一周内时，一周被切成两片 - 一个记录的开始日期是一周的开始，结束日期是该月的最后一天，以及一个包含一周剩余时间日期的记录（开始日期是新月份的第一天，结束日期是一周的最后一天）。
Example (beginning of week is monday):calendar week; week start date; week end date;
...
2009 cW48; 23.11.2009; 29.11.2009
--"normal" week with 7 days, beginning monday and ending sunday
2009 cW49; 30.11.2009; 30.11.2009
--first part of the CW49, which ends at last day of the month
2009 cW49; 01.12.2009; 06.12.2009
--second part of the CW49, which begins at fist day of the new month
2009 cW50; 07.12.2009; 13.12.2009
--"normal" week, without a monthly break
...
示例（一周的开始是星期一）：calendar week; week start date; week end date;
...
2009 cW48; 23.11.2009; 29.11.2009
--"normal" week with 7 days, beginning monday and ending sunday
2009 cW49; 30.11.2009; 30.11.2009
--first part of the CW49, which ends at last day of the month
2009 cW49; 01.12.2009; 06.12.2009
--second part of the CW49, which begins at fist day of the new month
2009 cW50; 07.12.2009; 13.12.2009
--"normal" week, without a monthly break
...
How to create such a table in Oracle (SQL or PL SQL)?
如何在 Oracle（SQL 或 PL SQL）中创建这样的表？
Thank you,
Regards
Nadine
谢谢，
问候
纳丁
回答by Vincent Malgrat
you could create your table like this:
你可以像这样创建你的表：
SQL> CREATE TABLE weeks AS
2 WITH generator AS (
3 SELECT DATE '2007-01-01' + LEVEL - 1 dt
4 FROM dual
5 CONNECT BY LEVEL <= SYSDATE - DATE '2007-01-01' + 1
6 )
7 SELECT to_char(dt, 'YYYY "cW"IW') "calendar week",
8 dt "week start date",
9 least(next_day(dt - 1, to_char(DATE '2007-01-07', 'DAY')),
10 last_day(dt)) "week end date"
11 FROM generator
12 WHERE to_char(dt, 'D') = to_char(DATE '2007-01-01', 'D') -- only mondays
13 OR to_char(dt, 'dd') = 1 --or first day of the month
14 ;
Table created
SQL> SELECT *
2 FROM weeks
3 WHERE "week start date" BETWEEN DATE '2009-11-15' AND DATE '2009-12-15';
calendar week week start date week end date
------------- --------------- -------------
2009 cW47 16/11/2009 22/11/2009
2009 cW48 23/11/2009 29/11/2009
2009 cW49 30/11/2009 30/11/2009
2009 cW49 01/12/2009 06/12/2009
回答by karsany
this is a small PL/SQL block to create your table. Change the table names, if you want.
这是一个用于创建表的小型 PL/SQL 块。如果需要，更改表名。
-- create table weeks(year number, week number, b_date date, e_date date);
DECLARE
i DATE;
s DATE;
wk NUMBER;
yr NUMBER;
FUNCTION getweek(l DATE) RETURN NUMBER IS
BEGIN
-- !! week of year, iso standard, (31. dec can be on the first week of next year) !!
RETURN to_char(l, 'IW');
END;
BEGIN
i := to_date('2007-01-01', 'yyyy-mm-dd');
s := i;
DELETE weeks;
WHILE i <= to_date('2009-12-31', 'yyyy-mm-dd') LOOP
IF trunc(s, 'MONTH') <> trunc(i, 'MONTH') OR
getweek(s) <> getweek(i) THEN
wk := getweek(s);
yr := to_char(s, 'YYYY');
INSERT INTO weeks VALUES (yr, wk, s, i - 1);
s := i;
i := s;
END IF;
i := i + 1;
END LOOP;
i := i - 1;
wk := getweek(s);
yr := to_char(s, 'YYYY');
INSERT INTO weeks VALUES (yr, wk, s, i);
COMMIT;
END;
回答by Egor Rogov
You can create the table this way:
您可以通过以下方式创建表：
create table weeks(cw, start_date, end_date)
as
select to_char(gen.d,'YYYY "cW"IW')
, min(gen.d)
, max(gen.d)
from (
select to_date('01.01.2007','DD.MM.YYYY') + level -1 d
from dual
connect by level <= 1500 -- approx. number of days
) gen
group by
to_char(gen.d,'YYYY "cW"IW')
, to_char(gen.d,'YYYY MM IW')
having min(gen.d) <= sysdate
The point is in use IWand MMformat mask to get number of week and month, and then group results by both of them.
重点是使用IW和MM格式化掩码以获取周数和月数，然后按两者对结果进行分组。
