SQL匹配持续时间(从Datetime到Datetime)

Question

我有两个表

表A

开始时间结束时间

	2021.01.01 7:15:00	2021.01.019:3:00

首先将B与A连接起来，其中两个范围重叠(即一个范围的某个部分与另一范围的某一部分重叠(。一旦找到重叠的行，如有必要，请钳制B的开始和结束日期：

SELECT
CASE WHEN a.StartTime > b.StartTime THEN a.StartTime ELSE b.StartTime END AS s,
CASE WHEN a.EndTime   < b.EndTime   THEN a.EndTime   ELSE b.EndTime   END AS e
FROM b
JOIN a ON a.EndTime > b.StartTime AND b.EndTime > a.StartTime

DB<gt；Fiddle

我会对包括B在内的A进行内部联接，然后使用row_number进行排序：

select 
A.StartTime as OriginalStartTime
,A.EndTime as OriginalStartTime
,row_number() over (order by B.StartTime) as portion
,B.StartTime as PortionStartTime
,B.EndTime as PortionEndTime
from
TableA as as
inner join TableB as B on B.StartTime>=A.StartTime and B.EndTime<=A.EndTime
order by 3 asc