假设一个表中的数据如下:
id data11 4
12 8
13 24
14 36
15 50
id是自动增加的。我要得到结果如下:
6
16
30
43也就是说按照ID来排序的话,(下一条记录 + 上一条记录)/ 2。
请问这样的SQL语句如何实现?
谢谢先!
id data11 4
12 8
13 24
14 36
15 50
id是自动增加的。我要得到结果如下:
6
16
30
43也就是说按照ID来排序的话,(下一条记录 + 上一条记录)/ 2。
请问这样的SQL语句如何实现?
谢谢先!
Select
(data+(Select TOP 1 data From TEST Where ID<A.ID Order By ID Desc))/2 As data
From TEST A
Where data Is Not Null
Create Table TEST(ID Int Identity(1,1),data Int)
Insert TEST Select 4
Union All Select 8
Union All Select 24
Union All Select 36
Union All Select 50
GO
Select * From (
Select
(data+(Select TOP 1 data From TEST Where ID<A.ID Order By ID Desc))/2 As data
From TEST A) A
Where data Is Not Null
GO
Drop Table TEST
--Result
/*
6
16
30
43
*/
declare @t table(id int identity(1,1),data int)
insert @t select 4
insert @t select 8
insert @t select 24
insert @t select 36
insert @t select 50
select 结果=((select data from @t where id=a.id-1)+data)/2 from @t a
declare @t table(id int identity(1,1),data int)
insert @t select 4
insert @t select 8
insert @t select 24
insert @t select 36
insert @t select 50select * from
(
select 结果=((select data from @t where id=a.id-1)+data)/2 from @t a
) b
where 结果 is not null
(
select (A+a.A)/2
from @a
where id=(select min(id) from @a where id>a.id)
) as Value
from @a a
where (
select (A+a.A)/2
from @a
where id=(select min(id) from @a where id>a.id)
) is not null
Select (A.data+B.data)/2 As data From TEST A Inner Join TEST B On A.ID=B.ID-1
^^
考虑到断号就要用2重的子查询
------------------------------------------
不需要啊,我的那個就可以。