关于同一表中满足条件后的最后两条记录相减的操作的语句该怎么写？

Question

如例表：
(ASN列为自动增量编号，ID为标识，NUM是用于需要计算的数)
tablename:T1
ASN   ID   NUM   
--------------
1     12   100    
2     13   200
3     14   300 
4     12   110
5     14   320
6     13   230
7     14   365
8     13   250
9     12   160想达到以下目的：
按相同ID的最后两行（按ASN降序排列）用来计算，
把相同ID的ASN编号最大的一行的NUM列的数值 减去 ASN编号第二大的行的NUM列数值
后生成表单：
ID   NUM   
--------
12   50   （此条相当于ASN=9 的[NUM] 减去ASN=4 的[NUM],160-110=50）
13   20   （此条相当于ASN=8 的[NUM] 减去ASN=6 的[NUM],250-230=20）
14   45   （此条相当于ASN=7 的[NUM] 减去ASN=5 的[NUM],365-320=45）
这句查询该如何写？如同时处理这样的所有ID产生表单而不能实现的话，能否做到仅对一个ID而言来做，
也就是仅查询ID=x后的ASN最大两行的NUM列相减。
产生表单：
ID   NUM   
--------
13   20   （仅查询ID=13。相当于ASN=8 的[NUM] 减去ASN=6 的[NUM],250-230=20）以上这样的查询如何写？（不想用UPDATE,或生成到其他表中再来计算，想一步完成产的查询结果）
请各位赐教！

Answer 1

declare @T1 table (
ASN   int,
ID   int,
NUM   int
)
insert @t1 select
1,     12,   100    
union all select
2,     13,   200
union all select
3,     14,   300 
union all select
4,     12,   110
union all select
5,     14,   320
union all select
6,     13,   230
union all select
7,     14,   365
union all select
8,     13,   250
union all select
9,     12,   160
select 
   a.id,
   a.num-isnull(b.num,0) as num 
from 
   @t1 a
left join 
   @t1 b
on
   a.id=b.id   and 
   a.asn>b.asn and
   (select count(*) from @t1 where id=b.id and asn>=b.asn)=2
where (select count(*) from @t1 where id=a.id and asn>=a.asn)=1
order by a.id--结果
id          num         
----------- ----------- 
12          50
13          20
14          45（所影响的行数为 3 行）

Answer 2

看看符合要求吗用left join是因为考虑有一个id只有一条数的可能

Answer 3

----创建测试数据
declare @t1 table(ASN int,ID int, NUM  int)
insert @t1
select 1,     12,   100 union all
select 2,     13,   200 union all
select 3,     14,   300 union all
select 4,     12,   110 union all
select 5,     14,   320 union all
select 6,     13,   230 union all
select 7,     14,   365 union all
select 8,     13,   250 union all
select 9,     12,   160----查询
SELECT isnull(t1.ID,t2.ID) as ID,isnull(t1.NUM,0) - isnull(t2.NUM,0) as NUM FROM
(select * from @t1 as a
where not exists(select 1 from @t1 where ID = a.ID and ASN > a.ASN)) as t1
FULL JOIN
(select * from @t1 as a
where (select count(*) from @t1 where ID = a.ID and ASN > a.ASN) = 1) as t2
ON t1.ID = t2.ID
ORDER BY ID/*结果
ID    NUM
------------------
12    50
13    20
14    45
*/

Answer 4

太爽了！结果完全满足需要。
left join了3次 ，如表数据量大的话会不会很慢呢？
数据可能是每秒钟增加20行。能讲讲性能的影响么？

Answer 5

SELECT A.id,A.num-isnull(B.num,0) as num FROM (select * FROM t1 WHERE asn in (SELECT MAX(asn) as asn FROM t1 GROUP BY id)) A ;
LEFT JOIN (select * FROM t1 WHERE asn in (select MAX(asn) as asn from t1 where asn not in (select MAX(asn) as asn from t1 grou by id) grou  by id)) B ;
ON A.id=B.id

Answer 6

一条而言:
create proc subNum(@id int)
as
begin 
select ID,(select top 1 Num from td where ID=@id order by ASN desc)-(select top 1 NUM from td where ID=@id and ASN not in(select top 1 ASN from td where ID=@id order by ASN desc) order by ASN desc) as NUM
from td
where id=@id
group by Id
endexec subNum 12
/*结果
12  50
*/
exec subNum 13
/*
13  20
*/
exec subNum 14
/*
14  45
*/