这样的sql语句怎样写

[newair]

有如下的一个表，
ID value
1 2
2 2
3 3
4 3
5 4
6 1
7 5
8 6
...

怎样把他的 value 字段的数据分段统计，就是ID相邻的几个value 相加，比如把上表相邻的两个纪录相加(就是相邻的纪录俩俩相加 或者 三三相加....)为
1 4
2 6
3 5
4 11
...
形式

[zjcxc]

select a.value+b.value
from (
select id,value from 表 where (id-1) % 2 =0
) a inner join (
select id,value from 表 where (id-1) % 2 =1
) b on a.id=b.id-1

[lilu207]

select sum(value),(id+1)/2
from your table
group by (id+1)/2

[CrazyFor]

select (id-1)/2+1,sum(bb.value) from 表 bb group by (id-1)/2

[newair]

如果不ID字段并不存在呢

[txlicenhe]

--1.创建一个合并的函数
ALTER function f_sum(@id int)
returns int
as
begin
declare @str int
set @str=0
select @str=@str+ value from 表 where id between @id and @id +2 -- 相邻三个
return(@str)
End

调用自定义函数得到结果
select distinct id,dbo.f_sum(id) from 表

[txlicenhe]

如果不ID字段并不存在呢

先用 select identity(int,1,1) as id,value into #tmp from 表　　建一个ID字段
再对#tmp进行如上操作。

[CrazyFor]

更正一下:

select (max(id)-1)/2+1,sum(bb.value) from 表 bb group by (id-1)/2

[chao778899]

如果ID字段不存在，需先创建一个ID字段，然后－－－
select identity(int,1,1) as newcol ,* into #www from table

[zjcxc]

如果你要显示编号,就用

select b.id/2 as value,a.value+b.value as value
from (
select id,value from 表 where (id-1) % 2 =0
) a inner join (
select id,value from 表 where (id-1) % 2 =1
) b on a.id=b.id-1

[zjcxc]

如果你要显示编号,就用

select b.id/2 as value,a.value+b.value as value
from (
select id,value from 表 where (id-1) % 2 =0
) a inner join (
select id,value from 表 where (id-1) % 2 =1
) b on a.id=b.id-1

[newair]

我用的是access

[zjcxc]

如果id字段不存在,就用一个临时表表解决.

select id=identity(int,0,1),value into #tb from 你的表

select a.id/2+1 as value,a.value+b.value as value
from (
select id,value from #tb where (id) % 2 =0
) a inner join (
select id,value from #tb where (id) % 2 =1
) b on a.id=b.id-1

drop table #tb

[zjcxc]

如果id字段不存在,就用一个临时表表解决.

select id=identity(int,0,1),value into #tb from 你的表

select a.id/2+1 as value,a.value+b.value as value
from (
select id,value from #tb where (id) % 2 =0
) a inner join (
select id,value from #tb where (id) % 2 =1
) b on a.id=b.id-1

drop table #tb

[zjcxc]

access数据库中,如果没有id的话,就用模块来实现:

[CrazyFor]

ACCESS这样也可以吧.

select (max(id)-1)/2+1,sum(bb.value) from 表 bb group by (id-1)/2