表T
字段A,B,C
有三条记录A B C
0 20 30
10 0 0
60 90 0求A,B,C的平均值,0的记录不做统计,如A列,结果为(10+60)/2要求一条语句实现
字段A,B,C
有三条记录A B C
0 20 30
10 0 0
60 90 0求A,B,C的平均值,0的记录不做统计,如A列,结果为(10+60)/2要求一条语句实现
解决方案 »
- SQL 2008 是在新建查询里面输入建表语句么?
- 请教一个问题,SQL2005的多核支持问题
- SQL SERVER2000中登录权限和用户权限的问题
- 建这样一个视图,多个表相同结构,具体需求如下
- 请问这句是什么意思:CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END AS MyCustomerID
- sqlserver数据库中级联的问题.
- 数字转换成英文大写(要求小数位数动态指定)!!!
- 不能查看存储过程了,提示(无法创建目录或文件)!!!!!!
- 如果禁止其他管理员查看数据库
- 请问,怎样将一个storedproc 所返回的数据集,转成txt 文件 ?,(用SQL语句),因为是写程序!
- 主键问题,问大侠们是为什么?
- 有谁帮帮我啊?
sum(b)/sum(case b when 0 then 0 else 1 end) as b,
sum(c)/sum(case c when 0 then 0 else 1 end) as c,
from t
,sum(B)/sum(case when B=0 then 0 else 1 end)
,sum(C)/sum(case when C=0 then 0 else 1 end)
from table
(a.A+a.B+a.C)/(case a.D when 0 then 1 else a.D end)
from
(select
A,B,C,
(case A when 0 then 0 else 1 end)+(case B when 0 then 0 else 1 end)+(case C when 0 then 0 else 1 end) as D
from
T) a
sum(A)/(case sum(case A when 0 then 0 else 1 end) when 0 then 1 else sum(case A when 0 then 0 else 1 end) end),
sum(B)/(case sum(case B when 0 then 0 else 1 end) when 0 then 1 else sum(case B when 0 then 0 else 1 end) end),
sum(C)/(case sum(case C when 0 then 0 else 1 end) when 0 then 1 else sum(case C when 0 then 0 else 1 end) end)
from
T
A=(SELECT AVG(A) FROM 表 WHERE A>0),
B=(SELECT AVG(B) FROM 表 WHERE B>0),
C=(SELECT AVG(C) FROM 表 WHERE C>0)
FROM 表
sum(b)/(select count(*) from abc where b<>0) as avg_b,
sum(c)/(select count(*) from abc where c<>0) as avg_c
from abc
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0
select
sum(A)/(case sum(case A when 0 then 0 else 1 end) when 0 then 1 else sum(case A when 0 then 0 else 1 end) end) as A,
sum(B)/(case sum(case B when 0 then 0 else 1 end) when 0 then 1 else sum(case B when 0 then 0 else 1 end) end) as B,
sum(C)/(case sum(case C when 0 then 0 else 1 end) when 0 then 1 else sum(case C when 0 then 0 else 1 end) end) as C
from
@t/*
A B C
----------- ----------- -----------
35 55 30
*/
case sum(b) when 0 then 0 else sum(b)/sum(case b when 0 then 0 else 1 end) end as b,
case sum(c) when 0 then 0 else sum(c)/sum(case c when 0 then 0 else 1 end) end as c,
from t
-------------------------------------------------------------------
declare @t table(A int,B int,C int)
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0
SELECT
A=isnull((SELECT AVG(A) FROM @t WHERE A<>0),0),
B=isnull((SELECT AVG(B) FROM @t WHERE B<>0),0),
C=isnull((SELECT AVG(C) FROM @t WHERE C<>0),0) /*
A B C
----------- ----------- -----------
35 55 30
*/
insert into @t select 0,20,30
insert into @t select 10, 0, 0
insert into @t select 60,90, 0
select (sum(A)/(select count(A) from @t where A>0))AS A,(sum(B)/(select count(B) from @t where B>0))AS B,(sum(C)/(select count(C) from @t where C>0))AS C from @t
结果:
-------------------------
A B C
----------- ----------- -----------
35 55 30(所影响的行数为 1 行)
zlp321002(众里寻它千百度,蓦然回首,那人却在灯火阑珊处。) 和qw12cn() 的由于表T的条件复杂(是我简化了)不做考虑,结帐了
A=(SELECT AVG(A) FROM temp WHERE A<>0),
B=(SELECT AVG(B) FROM temp WHERE B<>0),
C=(SELECT AVG(C) FROM temp WHERE C<>0)
FROM temp
(
a int,
b int,
c int
)
go
insert into T (a,b,c) values(0,20,30)
go
insert into T(a,b,c) values(10,0,0)
go
insert into T(a,b,c) values(60,90,0)
go
select sum(a)/sum(case a when 0 then 0 else 1 end) as a,
sum(b)/sum(case b when 0 then 0 else 1 end) as b,
sum(c)/sum(case c when 0 then 0 else 1 end) as c from T
go
大家可以试一下这种算法,这个问题不难!!!
insert into test
select 0,20,30
union all
select 10,0,0
union all
select 60,90,0select sum(A)/sum(case when A=0 then 0 else 1 end) as AverageA,
sum(B)/sum(case when B=0 then 0 else 1 end) as AverageB,
sum(C)/sum(case when C=0 then 0 else 1 end)as AverageC
from test