管理重叠的日期

发布于 2025-01-10 09:59:35 字数 2128 浏览 1 评论 0原文

如何编写一个过程,以便在添加新插入时,适当地添加行?

假设我有一张桌子:

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] type = "a"parameter = 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-01-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] type = "a"parameter = 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] type = "a"parameter = 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*

How to write a procedure so that when adding a new insert, rows are added appropriately?

Let's say i have a table:

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. First test case:

In table we have:

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] type = "a" parameter = 1

and when we want to insert:

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-01-20] type = "a" parameter = 1

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

2. Second test case:

In table we have:

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] type = "a" parameter = 1

and when we want to insert:

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] type = "a" parameter = 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. Third test case:

In table we have:

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] type = "a" parameter = 1

and when we want to insert:

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] type = "a" parameter = 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*

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

悍妇囚夫 2025-01-17 09:59:35

当您插入完全包含在现有日期范围中间的新日期范围时,您需要: INSERT 新范围; UPDATE 将现有范围更新为新范围之前的部分;并INSERT一个新范围,用于新范围之后的现有范围部分。所以总共需要 3 处更改。

同样,当您插入完全包含现有范围的新日期范围时,您需要: INSERT 新范围;并DELETE现有范围(或执行单个UPDATE语句)。

您可以对所有这些操作使用单个 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<>fiddle 此处

When you insert a new date range that is completely contained in the middle of an existing date range then you need to: INSERT of the new range; UPDATE the existing range to the portion of that range before the new range; and INSERT a new range for the portion of the existing range after the new range. So you need a total of 3 changes.

Similarly, when you insert a new date range that completely contains an existing range then you need to: INSERT the new range; and DELETE the existing range (or do a single UPDATE statement).

You can use a single MERGE statement for all of these actions:

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<>fiddle here

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文