select *,
average = (select average(value) from Ta where id <= a.id)
from Ta a
average = (select average(value) from Ta where id <= a.id)
from Ta a
解决方案 »
- 两台机子分别安装sql2008,怎样让b中的一个数据始终和a保持同步
- 我的数据库录入时怎么会出现?(问号)
- 求一条SQL断号统计语句
- 求一SQL语句!
- 高手请指教:循环递增n条数据
- 我有个很好的问题,请高手来做.在线等很急急急急急急急#$%$%^#$#@$%@#%$@#%$@#%$@#
- 在线等一个ASP与存储过程分页问题
- 请问一下,谁知道sqlsever2000个人版那里有的下载呀??
- SQL SERVER 2000 怎样查看库中的TABLE大小(即多少M)
- SQLServer分词搜索效率太低
- sql2005transactional replication为什么老报subscriptions masked as inactive?很急!
- check约束问题
insert @t select
1 , 10 union select
2 , 15 union select
3 , 10 union select
4 , 20 union select
5 ,12
select *,
average = case when (select count(1) from @t where id < a.id)< 2 then 0
else (select avg(value * 1.0) from (select top 2 value from @t where id < a.id order by id desc) t) end
from @t a /*
ID Value average
----------- ----------- ----------------------------------------
1 10 .000000
2 15 .000000
3 10 12.500000
4 20 12.500000
5 12 15.000000(所影响的行数为 5 行)
*/
insert @t select
1 , 10 union select
2 , 15 union select
3 , 10 union select
4 , 20 union select
5 ,12
select *,
average = cast(case when (select count(1) from @t where id < a.id)< 2 then 0
else (select avg(value * 1.0)
from (select top 2 value from @t where id < a.id order by id desc) t) end as numeric(12,2))
from @t a /*
ID Value average
----------- ----------- --------------
1 10 .00
2 15 .00
3 10 12.50
4 20 12.50
5 12 15.00(所影响的行数为 5 行)
*/
t.value,
case when id=1 or id=2 then 0 else (select avg(cast(value as float)) from table1 where id=t.id-1 or id=t.id-2) end
from table1 t
order by id
create table tb (id int,value int)
insert into tb
select 1, 10 union all
select 2, 15 union all
select 3, 10 union all
select 4, 20 union all
select 5, 12 select * from tbselect id,value,case when id<2 then 0 else convert(numeric(10,2),(select avg(convert(numeric(10,2),value)) from tb where id=a.id-1 or id=a.id-2)) end as average from tb a/*
id value average
---------------------------------
1 10 .00
2 15 10.00
3 10 12.50
4 20 12.50
5 12 15.00*/drop table tb
declare @t table(id int,value int)
insert into @t select 1,10
insert into @t select 2,15
insert into @t select 3,10
insert into @t select 4,20
insert into @t select 5,12select id,value,(select sum(value)*1.0/2 from @t where id>=a.id-2 and id<a.id and a.id-2!=0)
from @t a
id value
----------- ----------- --------------------
1 10 .000000
2 15 .000000
3 10 12.500000
4 20 12.500000
5 12 15.000000(所影响的行数为 5 行)
insert into @t select 1,10
insert into @t select 2,15
insert into @t select 3,10
insert into @t select 4,20
insert into @t select 5,12select id,value,isnull((select sum(value)*1.0/2 from @t where id>=a.id-2 and id<a.id and a.id-2!=0),0)
from @t a/*id value
----------- ----------- --------------------
1 10 .000000
2 15 .000000
3 10 12.500000
4 20 12.500000
5 12 15.000000(所影响的行数为 5 行)
的方式查出来的结果为空。但是看起来语句好像也是正确的。很简单,希望是正确的。
-----------------------------------------------------------------
楼主自己直接将null值转换一下就行了结果是正确的。。
declare @t table(ID int,Value int)
insert @t select
1 , 10 union select
2 , 15 union select
3 , 10 union select
4 , 20 union select
5 ,12
select
*,
avg=(select sum(value)/2.0 from @t where id<a.id-1 )
from @t a/*
ID Value avg
----------- ----------- -------------------
1 10 NULL
2 15 NULL
3 10 5.000000
4 20 12.500000
5 12 17.500000(所影响的行数为 5 行)
*/
为什么2换成3或者4,结果就不正确了呢?