本文介绍了PL / SQL拆分,根据黑色日期将日期分隔为新日期!的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧! 问题描述 29岁程序员,3月因学历无情被辞! 我可以说一个旅行日期和黑日期。 我将根据黑色日期将旅行日期分成几段。 注意:旅行日期可以在 0 - 9999之间99 99 示例: 旅行日期:旅行|开始日期|结束日期 T | 2011 01 04 | 2011 12 11 黑色日期: BO |开始日期|结束日期 A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29 异常结果: 新旅行|开始日期|结束日期 X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11 目视: NAME:date range 旅行日期:----- [------------------------- - ] - A: - [------] ------------------------- B:------ [---] ------------------------ C:---- ---------- [---] ---------------- D:------------- --- [------] ----------- 结果: X1:----------- [ - ] ------ -------------- X2:----------------------- [------ - ] - 示例2: p> 旅行日期: - [----------------------- ---------] - BO日期A:---- [------] -------------- ----------- BO日期B:---------------------- [------] - ------ BO日期C:-------------------- [---] ----------- - BO日期D:------------------ [------] ----------- 结果X1: - [ - ] -------------------------- ------ 结果X2:----------- [------] ------------------ 结果X3:----------------------------- [----] - 示例3: 旅行日期:] ----------------------------------- [ BO日期A:---- [------] ------------------------- BO日期B:--- ---------------------- [---] ------- BO日期C:-------- -------- [---] ---------------- BO日期D:------------- ----- [------] ----------- 结果X1:---] ----------- ---------------------- 结果X2:----------- [ - ] ------ ---------------- 结果X3:--------------------------- - [------- 我如何使用PL SQL? p> 谢谢。 以下是表和测试用例: DROP TABLE TRACES.TRAVEL CASCADE CONSTRAINTS; CREATE TABLE TRACES.TRAVEL ( START_DATE DATE, END_DATE DATE ); DROP TABLE TRACES.BLACK_OUT_DATES CASCADE CONSTRAINTS; CREATE TABLE TRACES.BLACK_OUT_DATES ( BO CHAR(1 BYTE), START_DATE DATE, END_DATE DATE ); * 测试案例1 -------------------------------------- ----------------------------- 预期成果: 01/01/0001 09/02/2011 16/02/2011 01/04/2011 21/04/2011 10/05/2011 16/06/2011 19/11/2011 30/11 / 2011 31/12/9999 目视:旅行: ------------------------ ---------------------------- BO: - [ - ] - -------------------------------------------- - ------------- [------] ----------------------------- ----------------------------- [---------------] ------ 结果: [ - ] --- [--------] ------- [-----] ---------------- [-----] * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'0001-01-01',DATE'9999-12-31'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-10',DATE'2011-02-15'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-04-02',DATE'2011-04-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-05-11',DATE'2011-06-15'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-11-20',DATE'2011-11-29'); --INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-05-09',DATE'2011-05-12'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL; * TEST CASE 2 -------------------------------------- ----------------------------- 预期成果: 01/01/2011 01/02/2011 07/05/2011 06/07/2011 21/07/2011 31/12/2011 目视:旅行: [ - -------------------------------------------------- ---] BO: - [----------------------] ------ ------------------------ --------------- [ - ] --- ---------------------------------- ------------ --------------------- [--------] ------------- - -------------------------------------- [--------] - ---- 结果: [ - ] --------------------- [------ ---] -------------- [----] * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-01-01',DATE'2011-12-31'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-02',DATE'2011-05-06'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-03-03',DATE'2011-03-05'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-07-07',DATE'2011-07-09'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-07-08',DATE'2011-07-20'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL; * TEST CASE 3 -------------------------------------- ----------------------------- 预期成果: 04/05/2011 03/06/2011 21/06/2011 07/08/2011 目视:旅行: [--------------- ---------------------------------------] BO: - [----------------------] -------------------- ---------- --------------- [ - ] ----------------- -------------------- -------------------------- ------- [--------] ------------- ---------------- ------------------------ [--------] ------ 结果: [ - ] --------------------- [---------] ---------- ---- [----] * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-04-02',DATE'2011-10-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-01-01',DATE'2011-05-03'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-06-04',DATE'2011-06-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-06-06',DATE'2011-06-08'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-08-08',DATE'2011-12-30'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-08-08',DATE'2011-12-30'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL * 测试案例4 -------------------------------------- ----------------------------- 预期成果: 21/02/2011 09/04/2011 26/04/2011 09/05/2011 目视:旅行: ---- [----------- --------------] ------------------------- BO: - [----] -------------------------------------- ---------- ---- [----] -------------------------- -------------------- ------------- [--------] --- ------------------------------ ---------------- - [ - ] ---------------------------------- ------ -------------------- [--------] -------------------- 结果: ---------- [ - ] -------- [ - ] ---------- ------------------- * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-02-10',DATE'2011-05-15'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-02',DATE'2011-02-15'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-02-10',DATE'2011-02-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-04-10',DATE'2011-04-25'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-04-15',DATE'2011-04-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-05-10',DATE'2011-05-20'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL; * 测试案例5 -------------------------------------- ----------------------------- 预期成果: 21/02/2011 04/05/2011 目视:旅行: ------ [----------------------- - ] ----------------------- BO: - [-----] - ---------------------------------------------- - [ - ] -------------------------------------------- ------ ---------------------------- [--------] - ---------------- ------------------------------ [-----] ------------------- -------------------- ----------- [ - ] --------------------- 结果: -------- [-------------------] --------------------- ------ * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-02-10',DATE'2011-05-17'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-05',DATE'2011-02-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-02-07',DATE'2011-02-09'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-05-05',DATE'2011-05-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-05-07',DATE'2011-05-15'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-05-09',DATE'2011-05-12'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL; * 测试案例6 -------------------------------------- ----------------------------- 预期结果:否结果 目视:旅行: ------ [----------------------------] - ------------------ BO: - [--------------- ------------------------] ------------- 结果:否结果 * / TRUNCATE TABLE TRACES.TRAVEL; TRUNCATE TABLE TRACES.BLACK_OUT_DATES; INSERT INTO TRACES.TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-02-10',DATE'2011-09-20'); INSERT INTO TRACES.BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-01-05',DATE'2011-10-10'); COMMIT; SELECT * FROM BLACK_OUT_DATES; SELECT * FROM TRAVEL; 解决方案您的表: SQL>创建表格旅行(start_date,end_date) 2 as 3选择日期2011-01-04,日期2011-12-11从双 4 / 创建表。 SQL>创建表black_out_dates(bo,start_date,end_date) 2 as 3选择'A',date'2010-11-01',date'2011-02-11'from dual union all 4选择'B',日期'2011-01-20',日期'2011-02-15'从双联合全部 5选择'C',日期'2011-03-13',日期2011 -04-10'from dual union all 6选择'D',date'2011-03-20',date'2011-06-29'from dual 7 / 创建表。 这个查询考虑到完全重叠的黑色时段: SQL>选择'X'|| to_char(row_number()over(order by new_start_date))new_travel 2,new_start_date 3,new_end_date 从(select end_date + 1 new_start_date 5,lead(start_date - 1 ,1,t_end_date)over(order by start_date)new_end_date 6 from(select start_date 7,end_date 8,t_end_date 9,row_number()over(order by start_date) rn_start_date 10,row_number()over(order by end_date)rn_end_date 11从(select bo.start_date 12,bo.end_date 13,t.end_date t_end_date 14从black_out_dates bo 15,旅行t 16其中t.start_date< = bo.end_date 17和t.end_date> = bo.start_date 18联合全部 19选择start_date - 1 20,start_date - 1 21,null 22从旅行 23) 24) 25其中rn_start_date 26) 27其中new_start_date< = new_end_date 28 order by new_start_date 29 / NEW_TRAVEL NEW_START_DATE NEW_END_DATE ---------- ------------------- --- ---------------- X1 16-02-2011 00:00:00 12-03-2011 00:00:00 X2 30-06- 2011 00:00:00 11-12-2011 00:00:00 选择2行。 如果您的black_out_dates表包含N行,那么最多N + 1个空格。该查询在[2011-01-03,2011-01-03]中组成一个虚拟黑屏日期,然后使用分析函数LEAD来确定下一个截止日期开始的位置。完全重叠的时间段由ROW_NUMBER个分析功能删除,因为它们会导致间隔时间的变化。 编辑14 -3-2011 使用这些表: SQL>创建表格旅行(start_date,end_date) 2 as 3选择日期'2001-01-04',日期'2013-12-11'从双 4 / 创建表。 SQL>创建表black_out_dates(bo,start_date,end_date) 2 as 3选择'A',date'2010-11-01',date'2011-02-11'from dual union all 4选择'B',日期'2011-01-20',日期'2011-02-15'从双联合全部 5选择'C',日期'2011-03-13',日期2011 -04-10'from dual union all 6选择'D',date'2011-03-20',date'2011-06-29'from dual 7 / 创建表。 我以前的查询仍然没有正确处理重叠的时间段。所以这里是一个修订版本,方便地存储在视图v: SQL>创建视图v 2为 3,t1为 4(选择bo.start_date 5,bo.end_date 6,t.end_date t_end_date 7从black_out_dates bo 8,旅行t 9其中bo.start_date< = t.end_date 10和bo.end_date> = t.start_date 11 union all 12选择start_date - 1 13,start_date - 1 14,end_date 15从旅行 16) 17,t2 as 18 (选择t1。* 19,nvl 20(max(end_date) 21超过 22(order by start_date,end_date desc 23行在无界前面和1前面的 24) 25,to_date('1','j') 26)max_date 27 from t1 28) 29, t3为 30(select start_date 31,end_date 32,t_end_date 33,sum(case w母鸡start_date> max_date then 1 else 0 end) 34 over 35(order by start_date,end_date desc)grp 36 from t2 37) 38,t4 as 39(select max(end_date)+ 1 new_start_date 40,lead(min(start_date) - 1,1,t_end_date)over(order by min(start_date))new_end_date 41 from t3 42 group by t_end_date 43,grp 44) 45 select new_start_date 46,new_end_date 47 from t4 48 where new_start_date 49 / 查看创建。 测试结果: SQL>设置反馈 SQL>备注测试1 SQL>选择*从v 2 / NEW_START_DATE NEW_END_DATE ------------------- ------- ------------ 04-01-2001 00:00:00 31-10-2010 00:00:00 16-02-2011 00:00:00 12-03-2011 00:00:00 30-06-2011 00:00:00 11-12-2013 00:00:00 SQL> comment Test 2 SQL>删除旅行 2 / SQL>删除black_out_dates 2 / SQL> INSERT INTO TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-01-01',DATE'2011-12-31'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-02',DATE'2011-05-06'); SQL> INSERT INTO BLACK_OUT_DATES(B,START_DATE,END_DATE)VALUES('B',DATE'2011-03-03',DATE'2011-03-05'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-07-07',DATE'2011-07-09'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-07-08',DATE'2011-07-20'); SQL>选择*从v 2 / NEW_START_DATE NEW_END_DATE ------------------- ------- ------------ 01-01-2011 00:00:00 01-02-2011 00:00:00 07-05-2011 00:00:00 06-07-2011 00:00:00 21-07-2011 00:00:00 31-12-2011 00:00:00 SQL>备注测试3 SQL>删除旅行 2 / SQL>删除black_out_dates 2 / SQL> INSERT INTO TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-04-02',DATE'2011-10-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-01-01',DATE'2011-05-03'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-06-04',DATE'2011-06-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-06-06',DATE'2011-06-08'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-08-08',DATE'2011-12-30'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-08-08',DATE'2011-12-30'); SQL>选择*从v 2 / NEW_START_DATE NEW_END_DATE ------------------- ------- ------------ 04-05-2011 00:00:00 03-06-2011 00:00:00 21-06-2011 00:00:00 07-08-2011 00:00:00 SQL>备注测试4 SQL>删除旅行 2 / SQL>删除black_out_dates 2 / SQL> INSERT INTO TRAVEL(START_DATE,END_DATE)VALUES(DATE'2011-02-10',DATE'2011-05-15'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-02',DATE'2011-02-15'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-02-10',DATE'2011-02-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-04-10',DATE'2011-04-25'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-04-15',DATE'2011-04-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-05-10',DATE'2011-05-20'); SQL>选择*从v 2 / NEW_START_DATE NEW_END_DATE ------------------- ------- ------------ 21-02-2011 00:00:00 09-04-2011 00:00:00 26-04-2011 00:00:00 09-05-2011 00:00:00 SQL>备注测试5 SQL>删除旅行 2 / SQL>删除black_out_dates 2 / SQL> INSERT INTO TRAVEL VALUES(DATE'2011-02-10',DATE'2011-05-17'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('A',DATE'2011-02-05',DATE'2011-02-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('B',DATE'2011-02-07',DATE'2011-02-09'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('C',DATE'2011-05-05',DATE'2011-05-20'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('D',DATE'2011-05-07',DATE'2011-05-15'); SQL> INSERT INTO BLACK_OUT_DATES(BO,START_DATE,END_DATE)VALUES('E',DATE'2011-05-09',DATE'2011-05-12'); SQL>选择*从v 2 / NEW_START_DATE NEW_END_DATE ------------------- ------- ------------ 21-02-2011 00:00:00 04-05-2011 00:00:00 SQL>备注Test 6 SQL>删除旅行 2 / SQL>删除black_out_dates 2 / SQL> INSERT INTO TRAVEL VALUES(DATE'2011-02-10',DATE'2011-09-20'); SQL> INSERT INTO BLACK_OUT_DATES VALUES('A',DATE'2011-01-05',DATE'2011-10-10'); SQL>选择*从v 2 / 问候, Rob。 I have lets say a "travel date" and black out dates.I will split the travel date into pieces according to the black out dates.Note: Travel Date can be between 0 - 9999 99 99Sample:Travel Date:Travel | START DATE | END DATET | 2011 01 04 | 2011 12 11Black Out Dates:BO | START DATE | END DATEA | 2010 11 01 | 2011 02 11B | 2011 01 20 | 2011 02 15C | 2011 03 13 | 2011 04 10D | 2011 03 20 | 2011 06 29Excepted Result:New Travel | START DATE | END DATEX1 | 2011 02 16 | 2011 03 12X2 | 2011 06 30 | 2011 12 11Visually:NAME : date rangeTravel Date : -----[--------------------------]--A : --[------]-------------------------B : ------[---]------------------------C : --------------[---]----------------D : ----------------[------]-----------Result :X1 : -----------[--]--------------------X2 : -----------------------[--------]--Sample 2:Travel Date : -[--------------------------------]--BO Date A : ----[------]-------------------------BO Date B : ----------------------[------]-------BO Date C : --------------------[---]------------BO Date D : ------------------[------]-----------Result X1 : -[--]--------------------------------Result X2 : -----------[------]------------------Result X3 : -----------------------------[----]--Sample 3:Travel Date : ]-----------------------------------[BO Date A : ----[------]-------------------------BO Date B : -------------------------[---]-------BO Date C : ----------------[---]----------------BO Date D : ------------------[------]-----------Result X1 : ---]---------------------------------Result X2 : -----------[--]----------------------Result X3 : -----------------------------[-------How can I do it using PL SQL ?Thanks.Here are the tables and test cases:DROP TABLE TRACES.TRAVEL CASCADE CONSTRAINTS;CREATE TABLE TRACES.TRAVEL( START_DATE DATE, END_DATE DATE);DROP TABLE TRACES.BLACK_OUT_DATES CASCADE CONSTRAINTS;CREATE TABLE TRACES.BLACK_OUT_DATES( BO CHAR( 1 BYTE ), START_DATE DATE, END_DATE DATE);/*TEST CASE 1-------------------------------------------------------------------Expected Results:01/01/0001 09/02/201116/02/2011 01/04/201121/04/2011 10/05/201116/06/2011 19/11/201130/11/2011 31/12/9999Visually:Travel:----------------------------------------------------BO:--[--]-------------------------------------------------------------[------]----------------------------------------------------------[---------------]------Result:[-]---[--------]-------[-----]----------------[-----]*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '0001-01-01', DATE '9999-12-31' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-10', DATE '2011-02-15' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-04-02', DATE '2011-04-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-11', DATE '2011-06-15' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-11-20', DATE '2011-11-29' );--INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL;/*TEST CASE 2-------------------------------------------------------------------Expected Results:01/01/2011 01/02/201107/05/2011 06/07/201121/07/2011 31/12/2011Visually:Travel:[------------------------------------------------------]BO:--[----------------------]---------------------------------------------[--]----------------------------------------------------------------------[--------]-----------------------------------------------------[--------]------Result:[--]---------------------[---------]--------------[----]*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-01-01', DATE '2011-12-31' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-05-06' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-03-03', DATE '2011-03-05' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-07-07', DATE '2011-07-09' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-07-08', DATE '2011-07-20' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL;/*TEST CASE 3-------------------------------------------------------------------Expected Results:04/05/2011 03/06/201121/06/2011 07/08/2011Visually:Travel:[------------------------------------------------------]BO:--[----------------------]---------------------------------------------[--]----------------------------------------------------------------------[--------]-----------------------------------------------------[--------]------Result:[--]---------------------[---------]--------------[----]*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-04-02', DATE '2011-10-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-01', DATE '2011-05-03' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-06-04', DATE '2011-06-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-06-06', DATE '2011-06-08' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-08-08', DATE '2011-12-30' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-08-08', DATE '2011-12-30' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL;/*TEST CASE 4-------------------------------------------------------------------Expected Results:21/02/2011 09/04/201126/04/2011 09/05/2011Visually:Travel:----[-------------------------]-------------------------BO:--[----]----------------------------------------------------[----]-----------------------------------------------------------[--------]---------------------------------------------------[--]------------------------------------------------------------[--------]--------------------Result:----------[--]--------[--]-----------------------------*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-15' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-02-15' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-10', DATE '2011-02-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-04-10', DATE '2011-04-25' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-04-15', DATE '2011-04-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-10', DATE '2011-05-20' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL;/*TEST CASE 5-------------------------------------------------------------------Expected Results:21/02/2011 04/05/2011Visually:Travel:------[-------------------------]-----------------------BO:-[-----]--------------------------------------------------[--]------------------------------------------------------------------------------[--------]------------------------------------------------[-----]--------------------------------------------------[--]---------------------Result:--------[-------------------]---------------------------*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-17' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-05', DATE '2011-02-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-07', DATE '2011-02-09' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-05', DATE '2011-05-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-05-07', DATE '2011-05-15' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL;/*TEST CASE 6-------------------------------------------------------------------Expected Results:No ResultVisually:Travel:------[----------------------------]--------------------BO:--[---------------------------------------]-------------Result:No Result*/TRUNCATE TABLE TRACES.TRAVEL;TRUNCATE TABLE TRACES.BLACK_OUT_DATES;INSERT INTO TRACES.TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-09-20' );INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-05', DATE '2011-10-10' );COMMIT;SELECT * FROM BLACK_OUT_DATES;SELECT * FROM TRAVEL; 解决方案 Your tables:SQL> create table travel (start_date,end_date) 2 as 3 select date '2011-01-04', date '2011-12-11' from dual 4 /Table created.SQL> create table black_out_dates (bo,start_date,end_date) 2 as 3 select 'A', date '2010-11-01', date '2011-02-11' from dual union all 4 select 'B', date '2011-01-20', date '2011-02-15' from dual union all 5 select 'C', date '2011-03-13', date '2011-04-10' from dual union all 6 select 'D', date '2011-03-20', date '2011-06-29' from dual 7 /Table created.And the query, which takes into account completely overlapping black out periods:SQL> select 'X' || to_char(row_number() over (order by new_start_date)) new_travel 2 , new_start_date 3 , new_end_date 4 from ( select end_date + 1 new_start_date 5 , lead(start_date - 1, 1, t_end_date) over (order by start_date) new_end_date 6 from ( select start_date 7 , end_date 8 , t_end_date 9 , row_number() over (order by start_date) rn_start_date 10 , row_number() over (order by end_date) rn_end_date 11 from ( select bo.start_date 12 , bo.end_date 13 , t.end_date t_end_date 14 from black_out_dates bo 15 , travel t 16 where t.start_date <= bo.end_date 17 and t.end_date >= bo.start_date 18 union all 19 select start_date - 1 20 , start_date - 1 21 , null 22 from travel 23 ) 24 ) 25 where rn_start_date <= rn_end_date 26 ) 27 where new_start_date <= new_end_date 28 order by new_start_date 29 /NEW_TRAVEL NEW_START_DATE NEW_END_DATE---------- ------------------- -------------------X1 16-02-2011 00:00:00 12-03-2011 00:00:00X2 30-06-2011 00:00:00 11-12-2011 00:00:002 rows selected.If your black_out_dates table contains N rows, then there at most N+1 gaps. The query makes up one dummy black out date period at [2011-01-03,2011-01-03], and then uses the analytic function LEAD to determine where the next black out date starts. Completely overlapping periods are removed by the ROW_NUMBER analytic functions, because they mess up the gap periods.EDIT 14-3-2011With these tables:SQL> create table travel (start_date,end_date) 2 as 3 select date '2001-01-04', date '2013-12-11' from dual 4 /Table created.SQL> create table black_out_dates (bo,start_date,end_date) 2 as 3 select 'A', date '2010-11-01', date '2011-02-11' from dual union all 4 select 'B', date '2011-01-20', date '2011-02-15' from dual union all 5 select 'C', date '2011-03-13', date '2011-04-10' from dual union all 6 select 'D', date '2011-03-20', date '2011-06-29' from dual 7 /Table created.My previous query still did not handle overlapping periods correctly. So here is a revised version, conveniently stored in view v:SQL> create view v 2 as 3 with t1 as 4 ( select bo.start_date 5 , bo.end_date 6 , t.end_date t_end_date 7 from black_out_dates bo 8 , travel t 9 where bo.start_date <= t.end_date 10 and bo.end_date >= t.start_date 11 union all 12 select start_date - 1 13 , start_date - 1 14 , end_date 15 from travel 16 ) 17 , t2 as 18 ( select t1.* 19 , nvl 20 ( max(end_date) 21 over 22 ( order by start_date,end_date desc 23 rows between unbounded preceding and 1 preceding 24 ) 25 , to_date('1','j') 26 ) max_date 27 from t1 28 ) 29 , t3 as 30 ( select start_date 31 , end_date 32 , t_end_date 33 , sum( case when start_date > max_date then 1 else 0 end ) 34 over 35 ( order by start_date, end_date desc ) grp 36 from t2 37 ) 38 , t4 as 39 ( select max(end_date) + 1 new_start_date 40 , lead(min(start_date) - 1, 1, t_end_date) over (order by min(start_date)) new_end_date 41 from t3 42 group by t_end_date 43 , grp 44 ) 45 select new_start_date 46 , new_end_date 47 from t4 48 where new_start_date <= new_end_date 49 /View created.And the test results:SQL> set feedback offSQL> remark Test 1SQL> select * from v 2 /NEW_START_DATE NEW_END_DATE------------------- -------------------04-01-2001 00:00:00 31-10-2010 00:00:0016-02-2011 00:00:00 12-03-2011 00:00:0030-06-2011 00:00:00 11-12-2013 00:00:00SQL> remark Test 2SQL> delete travel 2 /SQL> delete black_out_dates 2 /SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-01-01', DATE '2011-12-31' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-05-06' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-03-03', DATE '2011-03-05' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-07-07', DATE '2011-07-09' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-07-08', DATE '2011-07-20' );SQL> select * from v 2 /NEW_START_DATE NEW_END_DATE------------------- -------------------01-01-2011 00:00:00 01-02-2011 00:00:0007-05-2011 00:00:00 06-07-2011 00:00:0021-07-2011 00:00:00 31-12-2011 00:00:00SQL> remark Test 3SQL> delete travel 2 /SQL> delete black_out_dates 2 /SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-04-02', DATE '2011-10-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-01-01', DATE '2011-05-03' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-06-04', DATE '2011-06-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-06-06', DATE '2011-06-08' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-08-08', DATE '2011-12-30' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-08-08', DATE '2011-12-30' );SQL> select * from v 2 /NEW_START_DATE NEW_END_DATE------------------- -------------------04-05-2011 00:00:00 03-06-2011 00:00:0021-06-2011 00:00:00 07-08-2011 00:00:00SQL> remark Test 4SQL> delete travel 2 /SQL> delete black_out_dates 2 /SQL> INSERT INTO TRAVEL( START_DATE, END_DATE ) VALUES ( DATE '2011-02-10', DATE '2011-05-15' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-02', DATE '2011-02-15' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-10', DATE '2011-02-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-04-10', DATE '2011-04-25' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-04-15', DATE '2011-04-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-10', DATE '2011-05-20' );SQL> select * from v 2 /NEW_START_DATE NEW_END_DATE------------------- -------------------21-02-2011 00:00:00 09-04-2011 00:00:0026-04-2011 00:00:00 09-05-2011 00:00:00SQL> remark Test 5SQL> delete travel 2 /SQL> delete black_out_dates 2 /SQL> INSERT INTO TRAVEL VALUES ( DATE '2011-02-10', DATE '2011-05-17' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A', DATE '2011-02-05', DATE '2011-02-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B', DATE '2011-02-07', DATE '2011-02-09' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C', DATE '2011-05-05', DATE '2011-05-20' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D', DATE '2011-05-07', DATE '2011-05-15' );SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E', DATE '2011-05-09', DATE '2011-05-12' );SQL> select * from v 2 /NEW_START_DATE NEW_END_DATE------------------- -------------------21-02-2011 00:00:00 04-05-2011 00:00:00SQL> remark Test 6SQL> delete travel 2 /SQL> delete black_out_dates 2 /SQL> INSERT INTO TRAVEL VALUES (DATE '2011-02-10', DATE '2011-09-20' );SQL> INSERT INTO BLACK_OUT_DATES VALUES ('A', DATE '2011-01-05', DATE '2011-10-10' );SQL> select * from v 2 /Regards,Rob. 这篇关于PL / SQL拆分,根据黑色日期将日期分隔为新日期!的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-20 11:31
查看更多