如提供一个ch1=15.1的参量,求ch1=11.1 12.1 13.1 14.1 15.1时的ch2的平均值。先解决一个理想情况下的: declare @ch1 decimal(10,2) set @cl1=15.1 if (select * from table as a where ch1 in (@ch1,@ch1-1.0,@ch1-2.0,,@ch1-3.0,,@ch1-4.0))=5 select avg(cl2) from table as a where ch1 in (@ch1,@ch1-1.0,@ch1-2.0,,@ch1-3.0,,@ch1-4.0) 在这个句子中,table是个派生表: select * from [dbo].[data] where [id] in (select min([id]) from [dbo].[data] group by clo1)
--查询处理 declare @value real set @value=0.2select a.id,ch2=avg(b.ch2) from( select a.id ,id1=a.id+min(abs(a.id-b.ch1)) ,id2=a.id-min(abs(a.id-b.ch1)) from( select id=@value-4,[email protected],[email protected] union all select @value-3,@value-3.05,@value-2.95 union all select @value-2,@value-2.05,@value-1.95 union all select @value-1,@value-1.05,@value-0.95 union all select @value,@value-.05,@value+.05 )a,data b where b.ch1 between a.id1 and a.id2 group by a.id )a,data b where b.ch1 in(a.id1,a.id2) group by a.id
--测试(建议改用decimal这种精确的数据类型,否则计算会出现误差)--测试数据 CREATE TABLE data(id int NOT NULL,ch1 decimal(10,2),ch2 decimal(10,1)) insert data select 1 ,0.12,32.3 union all select 2 ,0.21,23.4 union all select 3 ,0.24,44.1 union all select 4 ,0.24,12.0 union all select 5 ,1.22,23.4 union all select 6 ,1.24,44.1 union all select 7 ,2.24,12.0 union all select 8 ,2.21,23.4 union all select 9 ,3.24,44.1 union all select 10 ,3.23,32.3 union all select 11 ,3.43,12.4 union all select 12 ,3.24,12.0 union all select 13 ,4.21,23.4 union all select 13 ,4.20,33.4 go--查询处理--查询参数 declare @value decimal(10,2) set @value=5.2--查询 select a.id,ch2=avg(b.ch2) from( select a.id ,id1=a.id+min(abs(a.id-b.ch1)) ,id2=a.id-min(abs(a.id-b.ch1)) from( select id=@value-4,[email protected],[email protected] union all select @value-3,@value-3.05,@value-2.95 union all select @value-2,@value-2.05,@value-1.95 union all select @value-1,@value-1.05,@value-0.95 union all select @value,@value-.05,@value+.05 )a,data b where b.ch1 between a.id1 and a.id2 group by a.id )a,data b where b.ch1 in(a.id1,a.id2) group by a.id go--删除测试 drop table data/*--测试结果id ch2 ------------- -------------- 1.20 23.400000 2.20 23.400000 3.20 32.300000 4.20 33.400000(所影响的行数为 4 行) --*/
declare @ch1 decimal(10,2)
set @cl1=15.1
if (select * from table as a where ch1 in (@ch1,@ch1-1.0,@ch1-2.0,,@ch1-3.0,,@ch1-4.0))=5
select avg(cl2) from table as a where ch1 in (@ch1,@ch1-1.0,@ch1-2.0,,@ch1-3.0,,@ch1-4.0)
在这个句子中,table是个派生表:
select * from [dbo].[data]
where [id] in (select min([id]) from [dbo].[data] group by clo1)
declare @value real
set @value=0.2select a.id,ch2=avg(b.ch2)
from(
select a.id
,id1=a.id+min(abs(a.id-b.ch1))
,id2=a.id-min(abs(a.id-b.ch1))
from(
select id=@value-4,[email protected],[email protected]
union all select @value-3,@value-3.05,@value-2.95
union all select @value-2,@value-2.05,@value-1.95
union all select @value-1,@value-1.05,@value-0.95
union all select @value,@value-.05,@value+.05
)a,data b
where b.ch1 between a.id1 and a.id2
group by a.id
)a,data b
where b.ch1 in(a.id1,a.id2)
group by a.id
CREATE TABLE data(id int NOT NULL,ch1 decimal(10,2),ch2 decimal(10,1))
insert data select 1 ,0.12,32.3
union all select 2 ,0.21,23.4
union all select 3 ,0.24,44.1
union all select 4 ,0.24,12.0
union all select 5 ,1.22,23.4
union all select 6 ,1.24,44.1
union all select 7 ,2.24,12.0
union all select 8 ,2.21,23.4
union all select 9 ,3.24,44.1
union all select 10 ,3.23,32.3
union all select 11 ,3.43,12.4
union all select 12 ,3.24,12.0
union all select 13 ,4.21,23.4
union all select 13 ,4.20,33.4
go--查询处理--查询参数
declare @value decimal(10,2)
set @value=5.2--查询
select a.id,ch2=avg(b.ch2)
from(
select a.id
,id1=a.id+min(abs(a.id-b.ch1))
,id2=a.id-min(abs(a.id-b.ch1))
from(
select id=@value-4,[email protected],[email protected]
union all select @value-3,@value-3.05,@value-2.95
union all select @value-2,@value-2.05,@value-1.95
union all select @value-1,@value-1.05,@value-0.95
union all select @value,@value-.05,@value+.05
)a,data b
where b.ch1 between a.id1 and a.id2
group by a.id
)a,data b
where b.ch1 in(a.id1,a.id2)
group by a.id
go--删除测试
drop table data/*--测试结果id ch2
------------- --------------
1.20 23.400000
2.20 23.400000
3.20 32.300000
4.20 33.400000(所影响的行数为 4 行)
--*/