管理重叠的日期

Question

如何编写过程，以便在添加新插入时适当添加行？

假设我有一张桌子：

create table test_table
(
code varchar2(10) not null,
type varchar2(50) not null,
start_date date not null,
end_date date not null,
parameter number
);

1.第一个测试用例：

在表格中我们有：

insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'),  to_date('10.01.2021', 'DD,MM,YYYY'), 1);

〔2021-01-01-2021-01-10〕类型＝"；a"；参数=1

当我们想插入时：

insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'),  to_date('20.01.2021', 'DD,MM,YYYY'), 1)

[2021-01-11-2021-21-20]类型="；a"；参数=1

*Result should be:
2021-01-01 - 2021-01-20 type = "a" parameter = 1*

---

2.第二个测试用例：

在表格中我们有：

insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'),  to_date('10.01.2021', 'DD,MM,YYYY'), 1)

[2021-01-01-2021-01-10]类型="；a"；参数=1

当我们想插入时：

insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'),  to_date('20.01.2021', 'DD,MM,YYYY'), 2)

[2021-01-06-2021-01-20]类型＝"；a"；参数=2

*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-20] type = "a" parameter = 2*

---

3.第三个测试用例：

在表格中我们有：

insert into test_table values ('CODE', 'a', to_date('01.01.2021', 'DD,MM,YYYY'),  to_date('10.01.2021', 'DD,MM,YYYY'), 1)

[2021-01-01-2021-01-20]类型＝"；a"；参数=1

当我们想插入时：

insert into test_table values ('CODE', 'a', to_date('06.01.2021', 'DD,MM,YYYY'),  to_date('15.01.2021', 'DD,MM,YYYY'), 2)

〔2021-01-06-2021-01-15〕类型＝"；a"；参数=2

*in result we should have:
[2021-01-01 - 2021-01-05] type = "a" parameter = 1
[2021-01-06 - 2021-01-15] type = "a" parameter = 2
[2021-01-16 - 2021-01-20] type = "a" parameter = 1*

Answer 1

当您插入一个完全包含在现有日期范围中间的新日期范围时，您需要：新范围的INSERT；CCD_ 2从现有范围到该范围在新范围之前的部分；并且CCD_ 3是用于在新范围之后的现有范围的部分的新范围。因此，您总共需要3个更改。

类似地，当您插入一个完全包含现有范围的新日期范围时，您需要：INSERT新范围；并且CCD_ 5是现有范围(或者执行单个CCD_ 6语句(。

您可以对所有这些操作使用单个MERGE语句：

MERGE INTO test_table dst
USING (
WITH new_data (code, type, start_date, end_date, parameter) AS (
SELECT 'CODE2', 'a', DATE '2021-01-01', DATE '2021-01-20', 2 FROM DUAL
)
SELECT NULL AS rid,
n.*,
0 AS status -- Insert
FROM   new_data n
UNION ALL
-- Existing rows overlapping before
SELECT t.ROWID,
t.code,
t.type,
t.start_date,
n.start_date - INTERVAL '1' DAY,
t.parameter,
1 -- Update overlap before
FROM   test_table t
INNER JOIN new_data n
ON (   t.start_date <= n.start_date
AND t.end_date   >= n.start_date)
UNION ALL
SELECT t.ROWID,
t.code,
t.type,
n.end_date + INTERVAL '1' DAY,
t.end_date,
t.parameter,
CASE
WHEN n.start_date <= t.end_date   AND t.end_date <= n.end_date
THEN 2 -- Delete
WHEN t.start_date < n.start_date  AND n.end_date <  t.end_date
THEN 0 -- Insert overlap afterwards
ELSE 1 -- Update overlap afterwards
END
FROM   test_table t
INNER JOIN new_data n
ON (   t.start_date <= n.end_date
AND t.end_date   >= n.start_date)
WHERE  NOT (t.start_date <= n.start_date AND t.end_date <= n.end_date)
) src
ON (src.rid = dst.ROWID AND status > 0)
WHEN MATCHED THEN
UPDATE
SET    code       = src.code,
start_date = src.start_date,
end_date   = src.end_date
DELETE
WHERE status = 2
OR    src.start_date > src.end_date
WHEN NOT MATCHED THEN
INSERT (code, type, start_date, end_date, parameter)
VALUES (src.code, src.type, src.start_date, src.end_date, src.parameter);

db<gt；小提琴这里