SQL计算时间交集

参考资料：《SQL进阶》P106 (鹿书)

关系（表）结构

现有一张住宿表(stay_people)如下

guest(入住客人)	start_date(入住时间)	end_date(退房时间)
阿良良木历	2006-10-26	2006-10-27
阿良良木月火	2006-10-28	2006-10-31
阿良良木火怜	2006-10-31	2006-11-01
忍野忍	2006-10-29	2006-11-01
忍野扇	2006-10-28	2006-11-02
战场原黑仪	2006-10-28	2006-10-30
千石抚子	2006-10-30	2006-11-02

问题：判断这些客人住店时间存在重叠，如果存在重叠，则展示客人的名字、入住时间和退房时间

问题分析

很明显这道题的重点是判断两个时间段是否相交，那么时间相交有如下三种情况：

答案

1.自关联然后判断是否为三种情况之一，如果符合一种，那么时间相交

1
2
3
4
5
6
7


select t1.guest
       ,t1.start_date
       ,t2.start_date
  from stay_people t1,stay_people t2
  where (t1.start_date<=t2.end_date and t1.start_date>=t2.start_date)
        or (t1.end_date>=t2.start_date and t1.start_date<=t2.start_date)
        or (t1.start_date>=t2.start_date and t1.end_date<=t2.end_date)

2.比较自关联后一行的最小的end_date和最大的start_date来判断两个时间段是否相交。

1
2
3
4
5


select t1.guest
       ,t1.start_date
       ,t2.start_date
  from stay_people t1,stay_people t2
  where greatest(t1.start_date,t2.start_date)<=least(t1.end_date,t2.end_date)

3 使用数据库的内置函数判断时间段是否相交

1
2
3
4
5


select t1.guest
       ,t1.start_date
       ,t2.start_date
  from stay_people t1,stay_people t2
  where (t1.start_date,t1.end_date) overlaps (t2.start_date,t2.end_date)

但是，这种判断默认的时间段是左闭右开的，即认为住宿时间为[start_date,end_date),并且这个函数只有SQL Server、PostgreSQL、Oracle支持，MySQL并不支持这种写法。未列举的数据库不一定不支持，可以查一下相关文档

PostgreSQL时间函数文档

我写的只是三种类型的处理方法，除了我的写法，还有许多别的写法，我只是做一个简单总结