一个看似简单却让人很头疼的SQL

Question

--建立测试环境
Create Table 表(工序号 varchar(10),合格数 integer)
--插入数据
insert into 表
select '1','100' union
select '10','90' union
select '20','80' union
select '30','70' union
select '35','60'
--测试语句
  select a.工序号,a.合格数-b.合格数 as 合格数 from
  (select id=(select count(1) from 表 where 工序号>a.工序号),* from 表 a )a,
  (select id=(select count(1)+1 from 表 where 工序号>a.工序号),* from 表 a)b 
  where a.id=b.id 
 
--删除测试环境
Drop Table 表

Answer 1

select 
    a.工序号,
    合格数 = a.合格数 - (select top 1 合格数 from AA where 工序号 > a.工序号 order by 工序号)
from
    AA a
where 
    exists(select 1 from AA where 工序号 > a.工序号)

Answer 2

--创建测试数据
create talbe AA(工序号 int,合格数 int)
insert into AA select 1 ,100
insert into AA select 10,90
insert into AA select 20,80
insert into AA select 30,70
insert into AA select 35,60--生成新表BB
select 
    a.工序号,
    合格数 = a.合格数 - (select top 1 合格数 from AA where 工序号 > a.工序号 order by 工序号)
into 
    BB
from
    AA a
where 
    exists(select 1 from AA where 工序号 > a.工序号)--查询BB表
select * from BB--查询结果
/*
工序号    合格数
----------------
1         10 
10        10 
20        10 
30        10
*/

Answer 3

select 
    a.工序号,
    合格数 = a.合格数 - isnull((select top 1 合格数 from AA where 工序号 > a.工序号 order by 工序号),0)
from
    AA a

Answer 4

declare @tb table(工序号 int,合格数 int)
insert into @tb
select               1,        100
 union all select              10,       90
 union all select               20,       80
 union all select               30 ,      70
 union all select               35,      60
select 工序号,合格数-(select 合格数 from @tb where 工序号=(select min(工序号) 工序号 from @tb where 工序号>t.工序号)) as 合格数
 from @tb as t