SQL查找多个重叠间隔中耗时

Question

不使用MSSQL或DB2或Oracle。
没有CTE。
没有OVERLAP谓词。
没有INTERVAL数据类型。
情况:要维修的车辆无法启动，直到
已收到订购工作的所有零件。
在开始维修之前，可能需要多次订购零件。
我们需要提取车辆处于“零件保持”状态的时间

因此，对于标识为id = 1的车辆
在4种不同情况下订购了零件(d1)，并收到了零件(d2)

    ID     d1     d2
     1     8/1    8/8
     1     8/2    8/6
     1     8/12   8/14
     1     8/3    8/10

 8/1                             8/8
  d1                              d2   
  |-------------------------------|  
         8/2             8/6                    8/12      8/14                  
         d1               d2                     d1        d2     
          |---------------|                      |----------|    
                   8/3                 8/10
                   d1                    d2
                   |---------------------|   
 8/1                                                       8/14
  |---------------------------------------------------------|  = 13 days
                                        8/10    8/12
  |--------------------------------------|    +  |----------|  = parts hold  = 11 days

从上方可以看出，开始工作的等待时间(假设8/1为
该车辆可用于工作的日期是13天。
等待零件的实际时间是11天，这是数字
我们需要从数据中得出。
实际的日期时间数据将是我们从中提取小时数的时间戳，
为了简化演示，我们在此示例数据中使用了日期。
我们正在努力生成一个基于集合(不是psm，不是udf，不是游标)的解决方案。
TIA

Answer 1

该SQL语句似乎可以获取所需的内容(t是样本表的表名):

SELECT
   d.id, 
   d.duration, 
   d.duration - 
   IFNULL(
      ( SELECT Sum( timestampdiff( SQL_TSI_DAY, 
                                   no_hold.d2, 
                                   ( SELECT min(d1) FROM t t4 
                                     WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 )))
        FROM ( SELECT DISTINCT id, d2 FROM t t1 
               WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) ) 
                       FROM t t2 WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 
             And d2 <> ( select max( d2 ) from t t3 where t3.id = t1.id )) no_hold
        WHERE no_hold.id = d.id ),
      0 ) "parts hold"
FROM 
   ( SELECT id, timestampdiff( SQL_TSI_DAY, min( d1 ), max( d2 ) ) duration
     FROM t GROUP BY id ) d

外部查询获取修复工作的持续时间。复杂子查询计算不等待零件的总天数。这可以通过以下步骤确定:不等待零件的开始日期，然后计算直到开始再次等待零件的天数:

// 1) The query for finding the starting dates when the vehicle is not waiting for parts, 
// i.e. finding all d2 that is not within any date range where the vehicle is waiting for part.
// The DISTINCT is needed to removed duplicate starting "no hold" period.

SELECT DISTINCT id, d2 
FROM t t1
WHERE ( SELECT sum( IIF( t1.d2 between t2.d1 and t2.d2, 1, 0 ) ) from t t2 
        WHERE t2.id = t1.id and t2.d2 <> t1.d2 ) = 0 AND 
      d2 <> ( SELECT max( d2 ) FROM t t3 WHERE t3.id = t1.id ) )

//2)车辆不等待零件的日期是上述查询直到车辆再次等待零件的日期

timestampdiff( SQL_TSI_DAY, no_hold.d2, ( SELECT min(d1) FROM t t4 WHERE t4.id = no_hold.id and t4.d1 > no_hold.d2 ) )

将以上两个时间段相结合并汇总所有此类时间段，就可以得出车辆不等待零件的天数。最终查询会添加一个额外条件，以计算外部查询中每个ID的结果。

这对于具有许多id的非常大的表来说可能不是很有效。如果id限制为一个或几个，应该没问题。