查找满足表中存在的某些日期约束的行

Question

我有一张这样的桌子

CustomerId     serviceID             from                           To
id1           serv1          '2016-01-01 07:22:33'       '2016-01-05 07:22:33'
id1           serv1          '2016-02-21 08:30:10'       '2016-02-27 08:30:10'
id1           serv2          '2016-02-15 18:30:10'       '2016-02-15 19:30:10'
id1           serv3          '2016-02-19 18:30:10'       '2016-02-19 19:30:10'
id1           serv4          '2017-01-01 08:30:10'       '2017-01-01 09:00:10'

其中：

 serv1 = "in Dog Hospital"
 serv2 = "X ray service"
 serv3 = "Recovering meal"

对于报告，我需要找到所有那些暗示“狗”在“狗医院”期间接受该服务的行，即狗在“狗医院”期间接受的那些服务。有什么建议吗？

Answer 1

您可以检查行，然后将其归入serv1的周期，如下所示：

select *
from t a
where exists (
    select 1
    from t b
    where serviceId = 'serv1'
    and a.`from` >= b.`from`
    and a.`to` <= b.`to`
);

Demo @ SQLFiddle