sql mysql 冲突_mysql – SQL按日期冲突

此查询查找重复项的数量：

select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups

from object_date od1

left join object_date od2

on od2.object_id = od1.object_id

and od2.end_date >= od1.start_date

and od2.start_date <= od1.end_date

and od2.id != od1.id

group by 1,2,3;

您可以使用此查询作为查询的基础,该查询可以准确地提供您所要求的内容(请参阅下面的输出).

select

object_id,

case dups when 0 then start_date else '' end as start_date,

case dups when 0 then end_date else '' end as end_date,

sum(dups) as dups

from (

select od1.object_id, od1.start_date, od1.end_date, sum(od2.id is not null) as dups

from object_date od1

left join object_date od2

on od2.object_id = od1.object_id

and od2.end_date >= od1.start_date

and od2.start_date <= od1.end_date

and od2.id != od1.id

group by 1,2,3) x

group by 1,2,3;

请注意,我使用了id列来区分行.但是,您可以将id的测试替换为与每列上的比较不匹配,即将od2.id！= od1.id替换为每个其他列不相等的测试,但这需要在所有其他列上使用唯一索引有意义,无论如何,拥有一个id列是一个好主意.

这是使用您的数据的测试：

create table object_date (

id int primary key auto_increment,

object_id int,

start_date int,

end_date int

);

insert into object_date (object_id, start_date, end_date)

values (1,1,5),(1,2,4),(1,6,8),(2,2,3);

针对此示例数据运行时输出第一个查询：

+-----------+------------+----------+------+

| object_id | start_date | end_date | dups |

+-----------+------------+----------+------+

| 1 | 1 | 5 | 1 |

| 1 | 2 | 4 | 1 |

| 1 | 6 | 8 | 0 |

| 2 | 2 | 3 | 0 |

+-----------+------------+----------+------+

针对此示例数据运行时输出第二个查询：

+-----------+------------+----------+------+

| object_id | start_date | end_date | dups |

+-----------+------------+----------+------+

| 1 | 6 | 8 | 0 |

| 1 | | | 2 |

| 2 | 2 | 3 | 0 |

+-----------+------------+----------+------+