我需要两分钟之间的时差.我有如下所示的开始时间和结束时间：
start time | End Time 11:15:00 | 13:15:00 10:45:00 | 18:59:00
我需要第一行的输出为45,60,15,分别对应于11:15和12:00,12：00和13：00,13：00和13:15之间的时差.
解决方法
以下按预期工作：
SELECT Diff = CASE DATEDIFF(HOUR,StartTime,EndTime)
WHEN 0 THEN CAST(DATEDIFF(MINUTE,EndTime) AS VARCHAR(10))
ELSE CAST(60 - DATEPART(MINUTE,StartTime) AS VARCHAR(10)) +
REPLICATE(',60',DATEDIFF(HOUR,EndTime) - 1) +
+ ',' + CAST(DATEPART(MINUTE,EndTime) AS VARCHAR(10))
END
FROM (VALUES
(CAST('11:15' AS TIME),CAST('13:15' AS TIME)),(CAST('10:45' AS TIME),CAST('18:59' AS TIME)),CAST('11:59' AS TIME))
) t (StartTime,EndTime);
要获得24列,您可以使用24个案例表达式,例如：
SELECT [0] = CASE WHEN DATEDIFF(HOUR,EndTime) = 0
THEN DATEDIFF(MINUTE,EndTime)
ELSE 60 - DATEPART(MINUTE,StartTime)
END,[1] = CASE WHEN DATEDIFF(HOUR,EndTime) = 1
THEN DATEPART(MINUTE,EndTime)
WHEN DATEDIFF(HOUR,EndTime) > 1 THEN 60
END,[2] = CASE WHEN DATEDIFF(HOUR,EndTime) = 2
THEN DATEPART(MINUTE,EndTime) > 2 THEN 60
END -- ETC
FROM (VALUES
(CAST('11:15' AS TIME),EndTime);
以下内容也有效,并且最终可能会比重复使用相同的case表达式更短：
WITH Numbers (Number) AS
( SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1
FROM (VALUES (1),(1),(1)) AS t1 (N)
CROSS JOIN (VALUES (1),(1)) AS t2 (N)
),YourData AS
( SELECT StartTime,EndTime
FROM (VALUES
(CAST('11:15' AS TIME),(CAST('09:45' AS TIME),CAST('11:59' AS TIME))
) AS t (StartTime,EndTime)
),PivotData AS
( SELECT t.StartTime,t.EndTime,n.Number,MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR,EndTime) = 0 THEN DATEDIFF(MINUTE,EndTime)
WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE,StartTime)
WHEN DATEDIFF(HOUR,t.StartTime,t.EndTime) <= n.Number THEN DATEPART(MINUTE,EndTime)
ELSE 60
END
FROM YourData AS t
INNER JOIN Numbers AS n
ON n.Number <= DATEDIFF(HOUR,EndTime)
)
SELECT *
FROM PivotData AS d
PIVOT
( MAX(MinuteDiff)
FOR Number IN
( [0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]
)
) AS pvt;
它通过连接到24个数字的表来工作,因此不需要重复case表达式,然后使用PIVOT将这24个数字滚动到列中
