set nocount on declare @t table (stcdt real,ZI real,QI real) insert into @t values(1,2,1) insert into @t values(2,6,null) insert into @t values(3,10,null) insert into @t values(12,17,15) insert into @t values(18,20,21)declare @Stcdt real,@Stcdt_up real,@Stcdt_down real set @Stcdt = 3 select top 1 @Stcdt_down=stcdt from @t where QI is not null and stcdt < @Stcdt order by 1 desc select top 1 @Stcdt_up=stcdt from @t where QI is not null and stcdt > @Stcdt order by 1if @Stcdt_down is null or @Stcdt_up is null return select ((((select ZI from @t where stcdt = @stcdt) - (select ZI from @t where stcdt = @Stcdt_down)) / ((select ZI from @t where stcdt = @Stcdt_up) - (select ZI from @t where stcdt = @Stcdt_down))) * ((select QI from @t where stcdt = @Stcdt_up) - (select QI from @t where stcdt = @Stcdt_down))) + (select QI from @t where stcdt = @Stcdt_down)
问题是这样的.给定一个 ZI,求它临近的两个值几可以了.
stcdt ZI QI
1 2 1
2 6 null
3 10 nUll
12 17 15
18 20 21如 给个ZI=10.2 它的临近的值为ZI 为 10,17
ZI=18 它的临近的值为ZI 为 17,20
SQL语句怎么写啊!帮帮我吧!
declare @t table (stcdt real,ZI real,QI real)
insert into @t values(1,2,1)
insert into @t values(2,6,null)
insert into @t values(3,10,null)
insert into @t values(12,17,15)
insert into @t values(18,20,21)declare @Stcdt real,@Stcdt_up real,@Stcdt_down real
set @Stcdt = 3 select top 1 @Stcdt_down=stcdt from @t where QI is not null and stcdt < @Stcdt order by 1 desc
select top 1 @Stcdt_up=stcdt from @t where QI is not null and stcdt > @Stcdt order by 1if @Stcdt_down is null or @Stcdt_up is null return
select ((((select ZI from @t where stcdt = @stcdt)
-
(select ZI from @t where stcdt = @Stcdt_down))
/
((select ZI from @t where stcdt = @Stcdt_up)
-
(select ZI from @t where stcdt = @Stcdt_down)))
*
((select QI from @t where stcdt = @Stcdt_up)
-
(select QI from @t where stcdt = @Stcdt_down)))
+
(select QI from @t where stcdt = @Stcdt_down)