我有一个表,Jobs,其中每一行代表一个由计算机程序执行的维护任务.它有这样的设计：

CREATE TABLE Jobs (
    JobId bigint PRIMARY KEY,...
    Status int NOT NULL,OriginalJobId bigint NULL
)

创建/启动作业时,其行将添加到表中,其状态为0.作业完成后,其状态将更新为1,当作业失败时,其状态将更新为2.当作业失败时,job-manager将通过复制失败作业的详细信息并将Status重置为0并在OriginalJobId中使用原始(失败的)JobId进行跟踪来将新行插入Jobs表中来重试该作业.如果此重新尝试失败,则应再次尝试最多3次,每次后续重试都将在OriginalJobId列中保留原始JobId.

我的问题是尝试制定一个查询来获取当前失败的作业集并获得重试次数.

这是表格中的示例数据：

JobId | Status | OriginalJobId
    1,1,NULL    -- Successful initial job
    2,NULL    -- Pending initial job
    3,2,NULL    -- Failed initial job
    4,3    -- Successful retry of Job 3
    5,NULL    -- Failed initial job
    6,5    -- Failed retry 1 of Job 5
    7,5    -- Failed retry 2 of Job 5 -- should be tried again for 1 more time
    8,NULL    -- Failed initial job
    9,8    -- Failed retry 1 of Job 8
   10,8    -- Failed retry 2 of Job 8
   11,8    -- Failed retry 3 of Job 8 -- don't try again
   12,NULL    -- Failed initial job

我的查询需要返回：

JobId | RetryCount
     5,2
    12,0

请注意如何不包括Job 3,因为它的上次重试成功(状态1).类似地,排除作业8,因为重试次数超过了限制3.包括作业5,因为它仍然失败并且仅重试了2次,并且包括作业12并且还没有重试.

我在想解决方案是这样的：

SELECT
    J1.JobId
FROM
    Jobs AS J1
    LEFT OUTER JOIN Jobs AS J2 ON J1.JobId = J2.OriginalJobId
WHERE
    J1.Status = 2

…但我想不出如何获取RetryCount数据.

这是我为这个问题创建的sqlFiddle,其中一个解决方案如下：

http://sqlfiddle.com/#!6/8765f

更新

这是一个更新的sqlFiddle,它比较了目前为止提供的5个解决方案(我添加了一个额外的HAVING子句来删除重试次数超过3次的作业)

http://sqlfiddle.com/#!6/8765f/23

性能方面,我认为GarethD的答案是最好的,因为它具有最简单的执行计划,并且倾向于以最快的时间在sqlFiddle中完成.

我的生产表有大约14,000,000行,所以显然结果会有所不同.我会在生产中尝试每一个,看看哪个是最快的,然后相应地选择答案.

谢谢大家的帮助！