SQL 查询 - 两行两列的值之间的差异

Question

我正在努力解决这个问题，使用T-SQL查询（SQL SERVER 2008）来解决以下问题：

Ky  ProductID  Start #  End #    Diff
1     100        10      12        0
2     100        14      20        2 (14 - 12)
3     100        21      25        1 (21 - 20)
4     100        30      33        5 (30 - 25) 
1     110        6       16        0
2     110        20      21        4 (20 - 16)
3     110        22      38        1 (22 - 21)

如您所见，我需要两行和两列中的值之间的差异。

我试过了

with t1 
( select ROW_NUMBER() OVER (PARTITION by ProductID ORDER BY ProductID, Start# ) as KY
       , productid
       , start#
       , end# 
  from mytable)

和

select DATEDIFF(ss, T2.complete_dm, T1.start_dm)
   , <Keeping it simple not including all the columns which I selected..>  
FROM T1 as T2 
RIGHT OUTER JOIN T1 on T2.Ky + 1  = T1.KY  
             and T1.ProductID = T2.ProductID 

上述查询的问题是，当 productID 从 100 更改为 110 时，它仍然会计算差异。

非常感谢修改

查询的任何帮助或任何更简单的解决方案。

谢谢

Answer 1

您可以尝试以下代码以获得所需的结果：

select ky,Start,[End],(select  [end]  from table1 tt where (tt.ky)=(t.ky-1) and tt.ProductID=t.ProductID) [End_Prev_Row],
       case ky when 1 then 0
       else (t.start -(select  [end]  from table1 tt where (tt.ky)=(t.ky-1) and tt.ProductID=t.ProductID)) 
       end as Diff      
from table1 t

SQL FIDDLE

Answer 2

尝试类似的东西。它应该给你你想要的差异。我在第一部分中获取每个产品的第一行，然后使用下一个Ky递归构建。

with t1 
as
( 
    select  ProductID, Ky, 0 as Difference, [End#]
    from mytable where ky = 1
    union all
    select m.ProductID, m.Ky, m.[Start#] - t1.[End#] as Difference, m.[End#]
    from mytable m
    inner join t1 on m.ProductID = t1.ProductID and m.Ky = t1.Ky + 1
)
select Ky, ProductID, Difference from t1
order by ProductID, Ky

Answer 3

正如 Anup 所提到的，您的查询似乎工作正常，我刚刚删除了 DateDiff 来计算差异，因为我假设列不是您示例中的 DATE 数据类型，我想这就是问题所在，请在下面找到修改后的查询

with t1  
as  
( select ROW_NUMBER() OVER (PARTITION by ProductID ORDER BY ProductID ) as KY  
   , productid  
   , st  
   , ed  
from YourTable)  
select T1.ProductID, t1.ST,t1.ED, ISNULL(T1.st - T2.ed,0) as Diff  
FROM T1 as T2  
RIGHT OUTER JOIN T1 on T2.KY+1 = T1.KY  
         and T1.ProductID = T2.ProductID   

Answer 4

SELECT ROW_NUMBER() OVER (PARTITION by rc.ContractID ORDER BY rc.ID) AS ROWID,rc.ID,rc2.ID,rc.ContractID,rc2.ContractID,rc.ToDate,rc2.FromDate
FROM tbl_RenewContracts rc
LEFT OUTER JOIN tbl_RenewContracts rc2
ON rc2.ID = (SELECT MAX(ID) FROM tbl_RenewContracts rcs WHERE rcs.ID < rc.ID AND rcs.ContractID = rc.ContractID) 
ORDER BY rc.ContractID

替换表名和列，并添加计算列以获取DATEDIFF。