SQL返回两行之间的秒差

这个问题与SQL Server有关。我有下表：

id    size   batch code   product code      additiontime
--------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00
2       91      55555       BigD Red        2017-05-15 13:00:05
3       94      55555       BigD Red        2017-05-15 13:00:15
4       91      44444       BigD Blue       2017-05-15 14:10:00
5       92      44444       BigD Blue       2017-05-15 14:15:00
6       93      44444       BigD Blue       2017-05-15 14:20:00
7       94      44444       BigD Blue       2017-05-15 14:30:00
8       91      33333       BigD Orange     2017-05-15 15:00:00
9       91      33333       BigD Orange     2017-05-15 15:00:10
10      94      33333       BigD Orange     2017-05-15 15:00:15

当我执行这个SQL语句时：

select *
from mytable y1
where size = 91 
  and not exists (select 1
                  from mytable y2
                  where y1.productcode = y2.productcode and y2.size = 92)

我得到以下结果：

id    size   batch code   product code      additiontime
--------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00
2       91      55555       BigD Red        2017-05-15 13:00:05
8       91      33333       BigD Orange     2017-05-15 15:00:00
9       91      33333       BigD Orange     2017-05-15 15:00:10

我想修改上面的查询，以便每个批处理代码显示一行，并添加一个附加列以显示每个批处理代码组的第一条记录和第二条记录之间的秒数差异，例如：

id    size   batch code   product code      additiontime            seconds difference
--------------------------------------------------------------------------------------
1       91      55555       BigD Red        2017-05-15 13:00:00     5
8       91      33333       BigD Orange     2017-05-15 15:00:00     10

我尝试过以下 SQL，它几乎可以解决问题，但它返回多行，而不是每批代码只返回一行：

WITH rows AS
(
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY AdditionTime) AS rn 
    FROM 
        mytable y1
    WHERE
        size = 91 
        AND NOT EXISTS (SELECT *
                        FROM mytable y2
                        WHERE y1.productcode = y2.productcode AND y2.size = 92)
)
SELECT DATEDIFF(second, mc.AdditionTime, mp.AdditionTime)
FROM rows mc
JOIN rows mp ON mc.rn = mp.rn - 1

我的 SQL 知识不是很多。我确信有可能产生正确的结果，但我已经尝试了所有方法但没有成功。预先感谢您的任何指点。

这将为您提供所需的输出，并对 cte 结果进行自连接。另外，我添加了一个PARTITION BY to the ROW_NUMBER():

CREATE TABLE #mytable
    (
      id INT ,
      size INT ,
      batchCode INT ,
      productCode NVARCHAR(30) ,
      additiontime DATETIME
    );


INSERT  INTO #mytable
        ( id, size, batchCode, productCode, additiontime )
VALUES  ( 1, 91, 55555, 'BigD Red', '2017-05-15 13:00:00' ),
        ( 2, 91, 55555, 'BigD Red', '2017-05-15 13:00:05' ),
        ( 3, 94, 55555, 'BigD Red', '2017-05-15 13:00:15' ),
        ( 4, 91, 44444, 'BigD Blue', '2017-05-15 14:10:00' ),
        ( 5, 92, 44444, 'BigD Blue', '2017-05-15 14:15:00' ),
        ( 6, 93, 44444, 'BigD Blue', '2017-05-15 14:20:00' ),
        ( 7, 94, 44444, 'BigD Blue', '2017-05-15 14:30:00' ),
        ( 8, 91, 33333, 'BigD Orange', '2017-05-15 15:00:00' ),
        ( 9, 91, 33333, 'BigD Orange', '2017-05-15 15:00:10' ),
        ( 10, 94, 33333, 'BigD Orange', '2017-05-15 15:00:15' );
WITH    rows
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY y1.batchCode ORDER BY additiontime ) AS rn
               FROM     #mytable y1
               WHERE    size = 91
                        AND NOT EXISTS ( SELECT *
                                         FROM   #mytable y2
                                         WHERE  y1.productCode = y2.productCode
                                                AND y2.size = 92 )
             )
    SELECT  t1.id ,
            t1.size ,
            t1.batchCode ,
            t1.productCode ,
            DATEDIFF(SECOND, t1.additiontime, t2.additiontime) SecondsDiff
    FROM    rows t1
            INNER JOIN rows t2 ON t2.batchCode = t1.batchCode
                                  AND t1.id != t2.id
    WHERE   t1.rn = 1; 

DROP TABLE #mytable

生产：

id  size    batchCode   productCode  SecondsDiff
8   91      33333       BigD Orange  10
1   91      55555       BigD Red     5

请注意，您应该使用更大的数据集进行测试，以确保准确性并考虑任何其他场景。