22,210
社区成员
发帖
与我相关
我的任务
分享
select * from tablename as a
where exists (select 1 from tablename as b where a.id!=b.id and a.车=b.车 and
((b.出车开始时间 between a.出车开始时间
and a.出车结束时间) or (b.出车结束时间 between a.出车开始时间
and a.出车结束时间)))
我的可以选出记录2 的。1楼夸号弄错了。对于记录2 存在记录1满足记录1的出车结束时间在记录2的出车开始时间和结束时间之内。
with cte as
(
select *,row_number() over(partition by 车 order by 出车开始时间 asc) as rId from tablename where 出车结束时间>=出车开始时间
)
select * from cte as a where exists (select * from cte where ((rId>a.rID and 出车开始时间<=a.出车结束时间) or (rId<a.rID and 出车结束时间 >=a.出车开始时间)) and 车=a.车)
with cte as
(
select *,row_number() over(partition by 车 order by 出车开始时间 asc) as rId from tablename
)
select * from cte as a where exists (select * from cte where ((rId=a.rID+1 and 出车开始时间<=a.出车结束时间) or (rId=a.rID-1 and 出车结束时间 >=a.出车开始时间)) and 车=a.车)
with cte as
(
select *,row_number() over(partition by 车 order by 出车开始时间 asc) as rId from tablename
)
select * from cte as a where exists (select * from cte where ((rId=a.rID+1 and 出车开始时间<=a.出车结束时间) or (rId=a.rID-1 and 出车结束时间 <=a.出车开始时间)) and 车=a.车)
select * from tablename as a
where exists (select 1 from tablename as b where a.id!=b.id and a.车=b.车 and
b.出车开始时间 between a.出车开始时间
and a.出车结束时间 or (b.出车结束时间 between a.出车开始时间
and a.出车结束时间))