Id wage
A 2000
A 200
B 2100
B 210
C 2200
C 220
D 2300
D 230
E 2400
E 240
希望得到查询结果
Id wage wage1
A 2000 2000
A 200 1800
B 2100 -300
B 210 -510
C 2200 -2710
C 220 -2930
D 2300 -5230
D 230 -5460
E 2400 -7860
E 240 -8100
------解决方案--------------------
2000以前要生成唯一id ,2005以后可以直接row_number()
------解决方案--------------------
假设源表名称为Test，对Test表做自联接，让前后行可以放在同一行相减：
-- 先为每行生成唯一标识 rn，后面就可以以此为联接字段，进行自联接
with T1 as
(
select rn = row_number() over(order by id, wage desc)
, id, wage
from Test
)
select T1.id, T1.wage
, wage1 = T1.wage - isnull(T2.wage,0)
from T1 left join
T1 as T2 on T1.rn = T2.rn+1
------解决方案--------------------
CREATE TABLE [dbo].[tbl1](
[id] [nvarchar](2) NULL,
[wage] [decimal](18, 10) NULL
) ON [PRIMARY]
insert into tbl1
select 'A', 2000
union all
select 'A',200
union all
select 'B',2100
union all
select 'B',210
union all
select 'C',2200
union all
select 'C', 220
union all
select 'D',2300
union all
select 'D',230
union all
select 'E',2400
union all
select 'E',240
GO
--------------------------------------------------------------------
----------------------------递归查询----------------------------
;with T1 as
(
select a.noid, a.id, a.wage, a.wage1 from (select (row_number()
over(order by id, wage desc)) as noid, id, wage, wage as wage1 from tbl1) as a where a.noid =1
union all
select b.noid, b.id, b.wage, CAST((c.wage1-b.wage1) AS decimal(18,10)) as wage1 from
(
select (row_number() over(order by id, wage desc)) as noid, id, wage, wage as wage1 from tbl1
)
as b inner join T1
as c on b.noid = c.noid+1
)
SELECT noid, id, wage, wage1 FROM T1
--------------------------------------------------------------------
-------------------------查询结果-------------------------------
1 A 2000.0000000000 2000.0000000000
2 A 200.0000000000 1800.0000000000
3 B 2100.0000000000 -300.0000000000
4 B 210.0000000000 -510.0000000000
5 C 2200.0000000000 -2710.0000000000
6 C 220.0000000000 -2930.0000000000
7 D 2300.0000000000 -5230.0000000000
8 D 230.0000000000 -5460.0000000000
9 E 2400.0000000000 -7860.0000000000
10 E 240.0000000000 -8100.0000000000
------解决方案--------------------
use tempdb
create table test (id varchar(4),wage int)
insert into test
select 'A', 2000 union all
select 'A', 200 union all
select 'B', 2100 union all
select 'B', 210 union all
select 'C', 2200 union all