sql - 表清理，查找时间范围内的重复帖子

我在几个表中都有这样的结构：id，[，validfrom，validto。

id是数字，并且validfrom和validto列的类型为DATE。任何给定的日期，每个id的帖子不得超过一个。

因此，这是一个正确的示例：

id, validfrom, validto
1, 2000-01-01, 2000-02-20
1, 2000-02-21, 2000-03-02
1, 2000-03-03, 2099-12-31

但是，似乎存在某些问题，其中某些日期将返回多个值。诸如此类（损坏的数据）：

id, validfrom, validto
1, 2001-01-01, 2001-02-20
1, 2001-01-15, 2001-03-02
1, 2001-03-03, 2099-12-31

因此，在上面的示例中，介于2001-01-15和2001-02-20之间的任何日期都将返回两行。

我将如何构建一个脚本来查找所有损坏的帖子？

最佳答案

只是为了找到它们，假定validfrom在每一行中都小于validto：

select a.*, b.*
from your_table a
join your_table b
on (a.id = b.id and
    --overlapping
    greatest(a.validfrom, b.validfrom) <= least(a.validto, b.validto) and
    --exclude join the same row.
    a.rowid <> b.rowid
    )

这只是找到相交的间隔，因为不同的间隔具有的valid_from大于另一个的valid_to。

UPDATE：我替换了条件not (a.validto=b.validto and a.validfrom=b.validfrom)
与

a.rowid<> b.rowid

因为它将立即报告重复的行。 （感谢沃尔菲）