sql - 重叠数据

Question

我有一个 sql 查询来检查表 PRODUCTS 中产品记录的重叠。在大多数情况下，查询工作正常，但以下情况除外。

select * from products where 
product_reg_no = 'AL-NAPT' 
and (to_date('14-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001')
or to_date('31-Aug-2001') BETWEEN to_date('27-Aug-2001') AND to_date('30-Aug-2001'))

如何使此查询捕获所有部分或完全重叠的记录？

如果需要，我可以提供带有示例记录的表结构。

谢谢

更新 1

我添加了表结构和记录here或者如下:

create table products
(product_reg_no varchar2(32),
 start_date date,
 end_date date);


Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/14/2012', 'MM/DD/YYYY'), TO_DATE('08/31/2012', 'MM/DD/YYYY'));
Insert into products
   (product_reg_no, START_DATE, END_DATE)
 Values
   ('AL-NAPT', TO_DATE('08/27/2012', 'MM/DD/YYYY'), TO_DATE('08/30/2012', 'MM/DD/YYYY'));
COMMIT;

从2012年8月14日到2012年8月31日的第一条记录与第二个记录是从2012年8月27日到2012年8月30日。那么如何修改我的查询以获得重叠？

Answer 1

参见 Determine whether two date ranges overlap .

您需要使用 <= 评估以下内容或它的次要变体而不是 < ，也许:

Start1 < End2 AND Start2 < End1

由于您使用的是单个表，因此您需要进行自连接:

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no != p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

不相等的条件确保您不会获得与其自身配对的记录(虽然 < 条件也确保了这一点，但如果您使用了 <=，不相等的条件将是一个好主意。

这将为每对产品生成两行(一行 ProductA 为 p1 和 ProductB 为 p2 ，另一行 ProductB 为 p1 和 ProductA 为 p2 )。为防止这种情况发生，请更改 !=进入<或 > .

---

并且，更仔细地查看示例数据，您可能真的对注册号匹配且日期重叠的行感兴趣。在这种情况下，你可以忽略我关于 != 的喋喋不休。和 <或 >并将条件替换为 =毕竟。

SELECT p1.*, p2.*
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start < p2.end
   AND p2.start < p1.end;

---

SQL Fiddle(未保存)表明这是可行的:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date);

WHERE 子句消除了连接到自身的行。删除 SELECT 列表中的重复列名后，您将看到所有数据。我添加了一行:

INSERT INTO products (product_reg_no, start_date, end_date)
VALUES ('AL-NAPT', TO_DATE('08/27/2011', 'MM/DD/YYYY'), TO_DATE('08/30/2011', 'MM/DD/YYYY'));

这没有被选中——表明它确实拒绝了非重叠的条目。

如果你想消除双行，那么你必须添加另一个奇特的标准:

SELECT p1.product_reg_no p1_reg, p1.start_date p1_start, p1.end_date p1_end,
       p2.product_reg_no p2_reg, p2.start_date p2_start, p2.end_date p2_end
  FROM products p1
  JOIN products p2
    ON p1.product_reg_no = p2.product_reg_no
   AND p1.start_date < p2.end_date
   AND p2.start_date < p1.end_date
 WHERE (p1.start_date != p2.start_date OR p1.end_date != p2.end_date)
   AND (p1.start_date < p2.start_date OR
       (p1.start_date = p2.start_date AND p1.end_date < p2.end_date));