重叠数据 [英] Overlapping Data
问题描述
我有一个sql查询来检查表PRODUCTS中的产品记录的重叠。
在大多数情况下，除了以下内容，查询可以正常工作。
select * from products where
product_reg_no = 'AL-NAPT'
和（to_date（'14 -Aug-2001'）BETWEEN to_date（'27 -Aug-2001'）AND to_date（'30 -Aug-2001'）
或to_date '31 -Aug-2001'）BETWEEN to_date（'27 -Aug-2001'）AND to_date（'30 -Aug-2001'））
如何使此查询捕获所有记录是部分还是完全重叠？
如果需要，我可以提供表结构
感谢
更新1
我已添加表格结构和记录此处或以下内容：
create table products
（product_reg_no varchar2（32），
start_date date，
end_date date）;
插入产品
（product_reg_no，START_DATE，END_DATE）
价值
（'AL-NAPT'，TO_DATE（'08 / 14/2012 '，'MM / DD / YYYY'），TO_DATE（'08 / 31/2012'，'MM / DD / YYYY'））;
插入产品
（product_reg_no，START_DATE，END_DATE）
值
（'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日的
第二条记录重叠。那么如何修改我的查询以获得重叠？
您需要评估或
$ 代替 ，可能： Start1< End2 AND Start2< End1
因为你使用单个表，你需要一个自联接： / p>
选择p1。*，p2。*
从产品p1
JOIN产品p2
ON p1.product_reg_no！= p2.product_reg_no
AND p1.start< p2.end
AND p2.start< p.end;
不等于条件确保您不会获得与自身配对的记录c $ c>< 条件也确保，但如果你使用< = ，不等于条件将是一个好主意。 / p>
这将为每对产品生成两行（一行ProductA为 p1 ，ProductB为 p2 ，另一个是ProductB为 p1 ，ProductA为 p2 要避免这种情况发生，请将！= 更改为< 或 code>。
而且，更仔细地查看示例数据，可能是在这种情况下，你可以忽略我对！= 和< 或> 替换 = 。
选择p1。*，p2。*
从产品p1
JOIN产品p2
ON p1.product_reg_no = p2.product_reg_no
AND p1.start< p2.end
AND p2.start< p.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
从产品p1
JOIN产品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或p1.end_date！= p2.end_date）;
WHERE子句会删除连接到自己的行。通过删除SELECT列表中的重复列名称，您可以查看所有数据。我添加了一行：
INSERT INTO产品（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
从产品p1
JOIN产品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或p1.end_date！= p2.end_date）
AND（p1.start_date< p2.start_date或
（p1.start_date = p2.start_date AND p1.end_date< p2.end_date））;
I have a sql query to check overlapping of product records in table PRODUCTS. In most cases query works fine except for the following.
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'))
How to make this query to catch all records are overlapping either partially or completely?
If required I can provide table structure with sample records.
Thanks
Update 1
I have added table structure and records here or as below:
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;
The first record which is from August, 14 2012 to August, 31 2012 is overlapping with second record which is from August, 27 2012 to August, 30 2012. So how can I modify my query to get the overlapping?
See Determine whether two date ranges overlap.
You need to evaluate the following, or a minor variant on it using <= instead of <, perhaps:
Start1 < End2 AND Start2 < End1
Since you're working with a single table, you need to have a self-join:
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;
The not equal condition ensures that you don't get a record paired with itself (though the < conditions also ensure that, but if you used <=, the not equal condition would be a good idea.
This will generate two rows for each pair of products (one row with ProductA as p1 and ProductB as p2, the other with ProductB as p1 and ProductA as p2). To prevent that happening, change the != into either < or >.
And, looking more closely at the sample data, it might be that you're really interesting in rows where the registration numbers match and the dates overlap. In which case, you can ignore my wittering about != and < or > and replace the condition with = after all.
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 (unsaved) shows that this works:
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);
The WHERE clause eliminates the rows that are joined to themselves. With the duplicate column names in the SELECT-list eliminated, you get to see all the data. I added a row:
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'));
This was not selected — demonstrating that it does reject non-overlapping entries.
If you want to eliminate the double rows, then you have to add another fancy criterion:
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));
这篇关于重叠数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
