请问SQL如何取出相连几笔纪录的平均值呢?

[developCpp]

请问如何取出相连几笔纪录的平均值呢?

比如表tb(id ,data)
id , data
1 , 3
2 , 4
3 , 5
4 , 13
5 , 14
6 , 15
7 , 23
8 , 24
9 , 25
10 , 33
11 , 34
12 , 35

取相连三笔纪录如下
4
14
24
34

[developCpp]

家中没有安装MS SQL Server , 用Access测试一下,结果不行哦
是否要对 (id-1)/3设置为只限制整数才行呢??
那么SQL语句该怎么写呢?

[developCpp]

樓上的
select (id+2)/3 as id,avg(data) as data from @t
group by (id+2)/3
結果是
id data
-------------
1 3
1.333.. 4
1.666.. 5
2 13
2.333.. 14
2.666.. 15
3 23
3.333... 24
3.6666....

[hellowork]

在SQLSERVER中,(id-1)/3或 (id+2)/3 返回的就是整数.T-SQL中,除法运算时,结果的精度取决于被除数,所以结果是整数而不是小数.
在ACCESS里不行是因为ACCESS的SQL语法与T-SQL语法不同.上面的例子中都是通过T-SQL测试的.
如果要在ACCESS中使用,就请楼主对(id-1)/3或 (id+2)/3取整试试.

[lxzm1001]

...

[playwarcraft]

create table tb(id int,data int)
insert into tb select 1,3
insert into tb select 2,4
insert into tb select 3,5
insert into tb select 4,13
insert into tb select 5,14
insert into tb select 6,15
insert into tb select 7,23
insert into tb select 8,24
insert into tb select 9,25
insert into tb select 10,33
insert into tb select 11,34
insert into tb select 12,35

select avg(data) from tb group by (id-1)/3


-----------
4
14
24
34

[lxzm1001]

select avg(data) from tablename where right(data,1)=4

[hellowork]

declare @t table(id int,data int)
insert @t
select 1 , 3 union all
select 2 , 4 union all
select 3 , 5 union all
select 4 , 13 union all
select 5 , 14 union all
select 6 , 15 union all
select 7 , 23 union all
select 8 , 24 union all
select 9 , 25 union all
select 10 , 33 union all
select 11 , 34 union all
select 12 , 35

select avg(data) from
(select idd = (id+2)/3,* from @t) a
group by idd

[playwarcraft]

要考慮小數點的問題就把data-->convert(numeric,data)

[hellowork]

select avg(data) as data from @t group by (id+2)/3

[playwarcraft]

group by (id+2)/3 ,如果要連續4個就不對了,
所以用group by (id-1)/3 比較有擴展性.

[hellowork]

to 楼上:
group by (id+2)/3 ,如果要連續4個就不對了,
所以用group by (id-1)/3 比較有擴展性.
----------------------------------------------------------------------------------
的确.但是如果要连续4个,就改为group by (id+3)/4.这样做的目的是有时可能要为汇总结果生成行号,使用这种方法生成的行号就是从1开始的,而楼上的方法生成的行号是从0开始的.例如:
select (id+2)/3 as id,avg(data) as data from @t
group by (id+2)/3
结果为:
id data
-------------------------------------
1 4
2 14
3 24
4 34