相邻记录值差

[knife_s]

假设记录如下

重量 移动极差
3
4
7

移动极差的计算方法如

当前记录的移动极差=此条记录重量-上条记录重量


结果如下
重量 移动极差
3 0
4 1
7 3


有没有SQL语句可以完成？

不胜感激

[liangCK]

SELECT id=IDENTITY(int,1,1),* INTO #tmp FROM tb;

SELECT 重量,移动极差=ISNULL(重量-(SELECT TOP 1 重量 FROM #tmp 
                                WHERE id < A.id ORDER BY id DESC),0)
FROM #tmp AS A;

[dawugui]

你最好还有个序号字段.

[7761098]

with tb
(
select row_number over(order by getdate()) as id, * from 表
)
select 重量,移动极差 = (a.移动极差-b.移动极差)
from tb a join tb b on a.id = b.id+1

[SQL77]

declare @tb table(num int)
insert @tb
select 3 union
select 4 union
select 7

select num ,id=identity(int,1,1) into #t from @tb


select t.num,isnull(t.num-t1.num,0)num1 from #t t1 right join #t t on t1.id=t.id-1
num num1
----------- -----------
3 0
4 1
7 3

（所影响的行数为 3 行）

[SQL77]

declare @tb table(num int)
insert @tb 
select 3 union 
select 4 union 
select 7

select num ,id=identity(int,1,1) into #t from @tb


select t.num,isnull(t.num-t1.num,0)num1 from #t t1 right join  #t t on t1.id=t.id-1
num         num1        
----------- ----------- 
3           0
4           1
7           3

（所影响的行数为 3 行）

[dawugui]

create table tb(id int,重量 int)
insert into tb values(1,3)      
insert into tb values(2,4)       
insert into tb values(3,7)
go

select * , 移动极差 = isnull(重量 - (select top 1 重量 from tb where id < t.id order by id desc),0) from tb t

drop table tb   

/*
id          重量          移动极差        
----------- ----------- ----------- 
1           3           0
2           4           1
3           7           3

（所影响的行数为 3 行）
*/

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([重量] int,[移动极差] sql_variant)
insert [TB]
select 3,null union all
select 4,null union all
select 7,null

select tid=identity(int,1,1),* into ## from TB

select 重量,移动极差=isnull(重量-(select 重量 from ## where T.tid=tid+1),0) from ## t

/*
重量          移动极差        
----------- ----------- 
3           0
4           1
7           3

（所影响的行数为 3 行）

*/
drop table ##,TB

[华夏小卒]

declare @ta table(num int)
insert @ta select
3 union select
4 union select
7 
select *,id=identity(int,1,1) into #t  from @ta 
  
select a.num,fz= a.num-isnull(b.num,a.num)
from #t a
left join #t b
 on a.id=b.id+1

num         fz
----------- -----------
3           0
4           1
7           3

(3 行受影响)
drop table #t 

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-10-28 10:59:09
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--	Nov 24 2008 13:01:59 
--	Copyright (c) 1988-2005 Microsoft Corporation
--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([重量] int)
insert [tb]
select 3 union all
select 4 union all
select 7
--------------开始查询--------------------------

select id=identity(int),* into #t from [tb]
select 
   重量,移动极差=isnull(重量-(select top 1 重量 from #t where id < t.id order by id desc),0)
from
   #t t;
----------------结果----------------------------
/* 重量          移动极差
----------- -----------
3           0
4           1
7           3

(3 行受影响)

*/

[csdyyr]

DECLARE @TB TABLE([重量] INT, [移动极差] INT)
INSERT @TB 
SELECT 3, 0 UNION ALL 
SELECT 4, 0 UNION ALL 
SELECT 7, 0

DECLARE @W INT, @D INT

UPDATE @TB SET @D=重量-ISNULL(@W,重量), 
		@W=重量, 
		 移动极差=@D
	

SELECT *
FROM @TB 


/*
重量          移动极差        
----------- ----------- 
3           0
4           1
7           3
*/

[xiequan2]

--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([重量] int)
insert [tb]
select 3 union all
select 4 union all
select 7


select *,id1=identity(int,1,1) into #t from tb

select [重量],移动极差=[重量]-isnull((select [重量] from #t where id1=t.id1-1),[重量]) from #t t

/*
重量          移动极差
----------- -----------
3           0
4           1
7           3
*/
drop table #t

[wujinjian2008n]

declare @tb table
(
	Weight int
)


insert into @tb (Weight) values(3)
insert into @tb (Weight) values(4)
insert into @tb (Weight) values(7)


select Weight,A=Weight-isnull(
(select Weight from
(select row_number() over(order by Weight asc) as ID,Weight from @tb) t where ID=tt.ID-1) ,weight)
from
(select row_number() over(order by Weight asc) as ID,Weight from @tb) tt

[knife_s]

谢谢了

由于是另一个程序的表，没有ID，现在要计算极差，

看样子非得用临时表呀

[dawugui]

[Quote=引用 13 楼 knife_s 的回复:]
谢谢了

由于是另一个程序的表，没有ID，现在要计算极差，

看样子非得用临时表呀
[/Quote]
那就只能用临时表了.

SELECT id=IDENTITY(int,1,1),* INTO #tmp FROM tb;

[-狙击手-]

declare @tb table(num int,col int)
insert @tb (num)
select 3 union
select 4 union
select 7


declare @i int ,@j int
select top 1 @i = num from @tb

update @tb
set col = num - @j ,@j = @I,@i = num


select * from @tb

[-狙击手-]

declare @tb table(num int,col int)
insert @tb (num)
select 3 union 
select 4 union 
select 7


declare @i int ,@j int
select top 1 @i =  num from @tb 

update @tb
set col = num -  @j ,@j = @I,@i = num


select * from @tb


num         col         
----------- ----------- 
3           0
4           1
7           3

（所影响的行数为 3 行）

[taoistong]

declare @tb table(num int,col int)
insert @tb (num)
select 3 union 
select 4 union 
select 7

select *,IDENTITY(int,1,1) taoistong into #taoistong from @tb

select 0 from #taoistong a where taoistong =1
union all 
select num-isnull((select min(num) from #taoistong b where a.taoistong=b.taoistong+1),0) from #taoistong a
where taoistong>1