我目前正在嘗試找出記錄之間的重疊時間段。我已經有一個查詢，表明它是否與“是”和“否”重疊另一個欄位。但是當欄位顯示“是”作為指示時，我需要有重疊時間段的確切持續時間。有人可以幫我嗎？
詢問：
CASE
WHEN
([StartDateTime] BETWEEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID]))
AND (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
([EndDateTime] BETWEEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID]))
AND (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
([StartDateTime] < (LAG([StartDateTime], 1) OVER (ORDER BY [aaID]))
AND [EndDateTime] > (LAG([EndDateTime], 1) OVER (ORDER BY [aaID]))) OR
([StartDateTime] > (LAG([StartDateTime], 1) OVER (ORDER BY [aaID]))
AND [EndDateTime] < (LAG([EndDateTime], 1) OVER (ORDER BY [aaID])))
THEN 'yes'
WHEN (LAG([StartDateTime], 1) OVER (ORDER BY [aaID])) IS NULL
THEN NULL
ELSE 'no'
END AS [OverLapping with Previous]
結果：
uj5u.com熱心網友回復：
檢查這個以天為單位的差異。
case when ([StartDateTime] >= (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] <= (lag([EndDateTime],1) over (order by [aaIDID])))
Then DateDiff(day, [StartDateTime], [EndDateTime])
when ([StartDateTime] <= (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] >= (lag([EndDateTime],1) over (order by [aaIDID])))
Then DateDiff(day, (lag([StartDateTime],1) over (order by [aaIDID])), (lag([EndDateTime],1) over (order by [aaIDID])))
when ([StartDateTime] < (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] < (lag([EndDateTime],1) over (order by [aaIDID]))) And [EndDateTime] > (lag([StartDateTime],1) over (order by [aaIDID]))
Then DateDiff(day, (lag([StartDateTime],1) over (order by [aaIDID])), [EndDateTime])
when ([StartDateTime] > (lag([StartDateTime],1) over (order by [aaIDID])) and [EndDateTime] > (lag([EndDateTime],1) over (order by [aaIDID]))) And [StartDateTime] < (lag([EndDateTime],1) over (order by [aaIDID]))
Then DateDiff(day, [StartDateTime], (lag([EndDateTime],1) over (order by [aaIDID])))
end as Diff
uj5u.com熱心網友回復：
一種蠻力方法，要在表中找到所有重疊的范圍對，是a.id < b.id在范圍重疊的地方將表與自身連接起來。id 檢查將抑制重復對和相同的行對。使用此處描述的邏輯檢查重疊。
一旦找到重疊對，計算重疊部分就很簡單了。
SELECT
a.id,
b.id,
CASE WHEN a.StartDateTime > b.StartDateTime THEN a.StartDateTime ELSE b.StartDateTime END AS overlap_start,
CASE WHEN a.EndDateTime < b.EndDateTime THEN a.EndDateTime ELSE b.EndDateTime END AS overlap_end
FROM yourtable AS a
JOIN yourtable AS b ON
a.id < b.id AND
b.EndTime > a.StartTime AND a.EndTime > b.StartTime
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/353208.html
標籤：sql sql-server 查询语句
上一篇：where條件中的OR子句