当我第一次进入sql世界时，我夜以继日地搜索这个问题的答案。找不到类似的东西来满足我的需求，所以我决定问并回答自己的问题，以防其他人像我一样需要帮助。
这是我所掌握的数据的一个例子。为了简单起见，它都来自Job表。每个作业ID都有自己的开始和结束时间，这些时间基本上是随机的，可以重叠、有间隙、与其他作业同时开始和结束等。
--Available--
JobID WorkerID JobStart JobEnd
1 25 '2012-11-17 16:00' '2012-11-17 17:00'
2 25 '2012-11-18 16:00' '2012-11-18 16:50'
3 25 '2012-11-19 18:00' '2012-11-19 18:30'
4 25 '2012-11-19 17:30' '2012-11-19 18:10'
5 26 '2012-11-18 16:00' '2012-11-18 17:10'
6 26 '2012-11-19 16:00' '2012-11-19 16:50'
我希望查询结果显示的是：
WorkerID TotalTime(in Mins)
25 170
26 120
编辑：忘了提到重叠需要忽略。基本上，这应该像对待钟点工而不是承包商一样对待这些工人和他们的工作。比如，如果我在中午12点到12点30分之间处理两个工作ID，并开始和完成这两个工作，作为一名员工，我只会得到30分钟的报酬，而承包商可能会得到60分钟的报酬。因为他们的工作是单独处理的，每个工作都有报酬。这个查询的目的是分析数据库中与员工相关的工作，并需要找出如果该员工被视为员工，他在给定时间内的总工作时间会是多少。
第二版：7个小时内不让我回答自己的问题，稍后会把它移到那里。
好的，现在回答问题。基本上，我使用临时表来构建我正在查找的工作的最短和最长日期时间之间的每一分钟。
IF OBJECT_ID('tempdb..#time') IS NOT NULL
BEGIN
drop table #time
END
DECLARE @FromDate AS DATETIME,
@ToDate AS DATETIME,
@Current AS DATETIME
SET @FromDate = '2012-11-17 16:00'
SET @ToDate = '2012-11-19 18:30'
create table #time (cte_start_date datetime)
set @current = @FromDate
while (@current < @ToDate)
begin
insert into #time (cte_start_date)
values (@current)
set @current = DATEADD(n, 1, @current)
end
现在我把所有的分钟都放在临时表里了。现在我需要将所有的工作表信息加入其中，并一次性选择我需要的内容。
SELECT J.WorkerID
,COUNT(DISTINCT t.cte_start_date) AS TotalTime
FROM #time AS t
INNER JOIN Job AS J ON t.cte_start_date >= J.JobStart AND t.cte_start_date < J.JobEnd --Thanks ErikE
GROUP BY J.WorkerID --Thanks Martin Parkin
drop table #time
这是一个非常简单的答案，让某人开始学习是件好事。
此查询也能完成此任务。它的性能非常好(虽然执行计划看起来不太好，但实际的CPU和IO击败了许多其他查询(。
看到它在Sql Fiddle中工作。
WITH Times AS (
SELECT DISTINCT
H.WorkerID,
T.Boundary
FROM
dbo.JobHistory H
CROSS APPLY (VALUES (H.JobStart), (H.JobEnd)) T (Boundary)
), Groups AS (
SELECT
WorkerID,
T.Boundary,
Grp = Row_Number() OVER (PARTITION BY T.WorkerID ORDER BY T.Boundary) / 2
FROM
Times T
CROSS JOIN (VALUES (1), (1)) X (Dup)
), Boundaries AS (
SELECT
G.WorkerID,
TimeStart = Min(Boundary),
TimeEnd = Max(Boundary)
FROM
Groups G
GROUP BY
G.WorkerID,
G.Grp
HAVING
Count(*) = 2
)
SELECT
B.WorkerID,
WorkedMinutes = Sum(DateDiff(minute, 0, B.TimeEnd - B.TimeStart))
FROM
Boundaries B
WHERE
EXISTS (
SELECT *
FROM dbo.JobHistory H
WHERE
B.WorkerID = H.WorkerID
AND B.TimeStart < H.JobEnd
AND B.TimeEnd > H.JobStart
)
GROUP BY
WorkerID
;
对于WorkerID, JobStart, JobEnd, JobID上的聚集索引，以及上面的样本7行，为新的工人/作业数据创建一个模板，重复足够多次以生成一个包含14336行的表，以下是性能结果。我已经在页面上列出了其他有效/正确的答案(到目前为止(：
Author CPU Elapsed Reads Scans
------ --- ------- ------ -----
Erik 157 166 122 2
Gordon 375 378 106964 53251
我在另一个(较慢的(服务器上做了一个更详尽的测试(每个查询运行25次，每个度量的最佳值和最差值被抛出，剩下的23个值被平均(，得到了以下结果：
Query CPU Duration Reads Notes
-------- ---- -------- ------ ----------------------------------
Erik 1 215 231 122 query as above
Erik 2 326 379 116 alternate technique with no EXISTS
Gordon 1 578 682 106847 from j
Gordon 2 584 673 106847 from dbo.JobHistory
我认为另一种技术一定会改善情况。嗯，它节省了6次读取，但花费了更多的CPU(这是有道理的(。与其将每个时间片的开始/结束统计信息一直带到最后，最好只是根据原始数据重新计算哪些片与EXISTS保持一致。可能是少数员工和许多工作的不同配置文件可能会更改不同查询的性能统计信息。
如果有人想尝试，请使用我的fiddle中的CREATE TABLE和INSERT语句，然后运行11次：
INSERT dbo.JobHistory
SELECT
H.JobID + A.MaxJobID,
H.WorkerID + A.WorkerCount,
DateAdd(minute, Elapsed + 45, JobStart),
DateAdd(minute, Elapsed + 45, JobEnd)
FROM
dbo.JobHistory H
CROSS JOIN (
SELECT
MaxJobID = Max(JobID),
WorkerCount = Max(WorkerID) - Min(WorkerID) + 1,
Elapsed = DateDiff(minute, Min(JobStart), Min(JobEnd))
FROM dbo.JobHistory
) A
;
我为这个查询构建了另外两个解决方案，但性能大约是原来的两倍的最好的解决方案有一个致命的缺陷(不能正确处理完全封闭的时间范围(。另一个有非常高/糟糕的统计数据(我知道，但必须尝试(。
解释
使用每行的所有端点时间，通过复制每个端点时间，然后分组，使每个时间与下一个可能的时间配对，建立一个感兴趣的所有可能时间范围的不同列表。只要这些范围与任何实际工人的工作时间相一致，就对其经过的分钟数求和。
以下查询应提供您要查找的答案：
SELECT WorkerID,
SUM(DATEDIFF(minute, JobStart, JobEnd)) AS TotalTime
FROM Job
GROUP BY WorkerID
很抱歉，它没有经过测试(我这里没有SQL Server来测试它(，但它应该能做到。
这是一个复杂的查询。说明如下。
with j as (
select j.*,
(select 1
from jobs j2
where j2.workerid = j.workerid and
j2.starttime < j.endtime and
j2.starttime > j.starttime
) as HasOverlap
from jobs j
)
select workerId,
sum(datediff(minute, periodStart, PeriodEnd)) as NumMinutes
from (select workerId, min(startTime) as periodStart, max(endTime) as PeriodEnd
from (select j.*,
(select min(starttime)
from j j2
where j2.workerid = j.workerid and
j2.starttime >= j.starttime and
j2.HasOverlap is null
) as thegroup
from j
) j
group by workerId, thegroup
) j
group by workerId;
理解这种方法的关键是理解"重叠"逻辑。当下一个开始时间在上一个结束时间之前时，一个时间段与下一个时间周期重叠。通过为每个记录分配一个重叠标志，我们可以知道它是否与"下一个"记录重叠。上面的逻辑使用的是开始时间。使用JobId可能会更好，尤其是如果同一工人的两个工作可以同时开始。
重叠标志的计算使用相关的子查询(这是with子句中的j(。
然后，对于每个记录，我们返回并找到overlap值为NULL的第一个记录。这为给定重叠集中的所有记录提供了一个分组关键字。
剩下的只是汇总结果，首先在workerId/组级别，然后在workerId级别，以获得最终结果。
我没有运行这个SQL，所以它可能有语法错误。