Diff abc
row1 3 xyz
row2 5 xyz
row3 10 xyz
row3 13 xyz
row3 15 xyz
And I want compare of two row's column like (row2.Diff - row1.Diff) into any other column.
我希望比较两行的列（如row2.Diff - row1.Diff）到任何其他列。
Result
2 (r2.diff - r1.diff)
5 (r3.diff - r2.diff)
3 (r4.diff - r3.diff)
2 (r5.diff - r4.diff)
2 个解决方案
#1
2
answers specific to sql server
特定于sql server的答案
;with cte
as
(
select *,row_number() over (order by diff) as rownum
from #tmp
)
select
c1.*,c2.diff-c1.diff as result
from cte c1
left join
cte c2 on c1.rownum+1=c2.rownum
if using SQLserver2012 or above..
如果使用SQLserver2012或更高版本..
select *,isnull(lead(diff) over (order by diff)-diff,0) as difff
from #tmp
#2
2
In SQL Server 2008, I would cross apply. The ordering of the rows is not 100% clear, but it seems based on diff:
在SQL Server 2008中，我会交叉申请。行的顺序不是100％清楚，但似乎基于diff：
select t.diff - t2.diff
from t cross apply
(select top 1 t2.*
from t t2
where t2.diff < t.diff
order by t2.diff desc
) t2;
#1
2
answers specific to sql server
特定于sql server的答案
;with cte
as
(
select *,row_number() over (order by diff) as rownum
from #tmp
)
select
c1.*,c2.diff-c1.diff as result
from cte c1
left join
cte c2 on c1.rownum+1=c2.rownum
if using SQLserver2012 or above..
如果使用SQLserver2012或更高版本..
select *,isnull(lead(diff) over (order by diff)-diff,0) as difff
from #tmp
#2
2
In SQL Server 2008, I would cross apply. The ordering of the rows is not 100% clear, but it seems based on diff:
在SQL Server 2008中，我会交叉申请。行的顺序不是100％清楚，但似乎基于diff：
select t.diff - t2.diff
from t cross apply
(select top 1 t2.*
from t t2
where t2.diff < t.diff
order by t2.diff desc
) t2;