如何根据多个时间段过滤数据

[研发群187380948欢迎加入]

上面这张表是a表，里面可能大概有几百万数据

这张表是b表里面大概有几条数据，具体条数不确定

现在要查询a表里，学上的上课时间段与b表冲突的数据

如何写sql 性能好些。

[Dear SQL(燊)]

在关联字段上加索引

select a.*
from a 
left join b on a.starttime and b.starttime and b.endtime
		and a.endtime and b.starttime and b.endtime
where b.starttime is null

[二月十六]

SELECT * FROM A,B WHERE a.startTime BETWEEN B.startTime AND B.endTime
UNION ALL
SELECT * FROM A,B WHERE a.endTime BETWEEN B.startTime AND B.endTime

[吉普赛的歌]

select * from a where exists(
    select 1 from b where b.startTime between a.startTime and a.endTime
)
union 
select * from a where exists(
    select 1 from b where b.endTime between a.startTime and a.endTime
)

[Dear SQL(燊)]

要优化将cast(a.starttime as time)，cast(a.endtime as time)生成新列，加索引

select a.*
from a 
left join b on cast(a.starttime as time) between cast(b.starttime as time) and cast(b.endtime as time)
		and cast(a.endtime as time) between cast(b.starttime as time) and cast(b.endtime as time)
where b.starttime is null

[Dear SQL(燊)]

引用 4 楼 chenshanliang 的回复:

要优化将cast(a.starttime as time)，cast(a.endtime as time)生成新列，加索引

select a.*
from a 
left join b on cast(a.starttime as time) between cast(b.starttime as time) and cast(b.endtime as time)
		and cast(a.endtime as time) between cast(b.starttime as time) and cast(b.endtime as time)
where b.starttime is null

这个跟公司的考勤差不多，上面写法跨天有问题，如果有跨天需将b生成每天的正常的上课单记录再比较

[研发群187380948欢迎加入]

三楼写的是对的，性能目测还可以，我是按照其他楼的写法写的，主要没有管理字段，我自己造的1 as管理字段，这么写的，会生成一个笛卡尔积。

[研发群187380948欢迎加入]

关联字段，看执行计划，内联效率更高些

[二月十六]

确实写的有问题，数据给弄多了 关联字段也会有这个问题，也可能使得数据更多，用ye版的吧，如果不去重的话union 换成 union all

[研发群187380948欢迎加入]

引用 4 楼 Dear SQL 的回复:

要优化将cast(a.starttime as time)，cast(a.endtime as time)生成新列，加索引

select a.*
from a 
left join b on cast(a.starttime as time) between cast(b.starttime as time) and cast(b.endtime as time)
		and cast(a.endtime as time) between cast(b.starttime as time) and cast(b.endtime as time)
where b.starttime is null

您好 请问生成新列如何加索引，能指导下吗？