Date a b c d
2009-1 4 2 2 5
2009-2 4 2 4 0
2009-3 2 0 0 7
求abcd列的平均数,
但是为0则不计算的平均数中: 比如说d列 (5+7)/2 =6 过滤掉0的那行
结果如下 avg(a) avg(b) avg(c) avg(a)
5 2 3 6
2009-1 4 2 2 5
2009-2 4 2 4 0
2009-3 2 0 0 7
求abcd列的平均数,
但是为0则不计算的平均数中: 比如说d列 (5+7)/2 =6 过滤掉0的那行
结果如下 avg(a) avg(b) avg(c) avg(a)
5 2 3 6
insert # select
'2009-1', 4, 2, 2 ,5 union all select
'2009-2', 4, 2, 4 ,0 union all select
'2009-3', 2, 0, 0 ,7
go
select
[AVG(a)]=(SELECT AVG(A) FROM # WHERE A<>0),
[AVG(B)]=(SELECT AVG(B) FROM # WHERE B<>0),
[AVG(C)]=(SELECT AVG(C) FROM # WHERE C<>0),
[AVG(D)]=(SELECT AVG(D) FROM # WHERE D<>0)
/*
AVG(a) AVG(B) AVG(C) AVG(D)
----------- ----------- ----------- -----------
3 2 3 6
*/
insert into tb select
'2009-1', 4, 2, 2, 5 union all select
'2009-2', 4, 2, 4, 0 union all select
'2009-3', 2, 0, 0, 7
select [avg(a)]=case when Sum(case a when 0 then 0 else 1 end)=0 then 0 else (sum(a)/Sum(case a when 0 then 0 else 1 end)) end
,[avg(b)]=case when Sum(case b when 0 then 0 else 1 end)=0 then 0 else (sum(b)/Sum(case b when 0 then 0 else 1 end)) end
,[avg(c)]=case when Sum(case c when 0 then 0 else 1 end)=0 then 0 else (sum(c)/Sum(case b when 0 then 0 else 1 end)) end
,[avg(d)]=case when Sum(case d when 0 then 0 else 1 end)=0 then 0 else (sum(d)/Sum(case d when 0 then 0 else 1 end)) end
from tb
/*avg(a) avg(b) avg(c) avg(d)
----------- ----------- ----------- -----------
3 2 3 6(1 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-05 19:52:16
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Date] varchar(6),[a] int,[b] int,[c] int,[d] int)
insert [tb]
select '2009-1',4,2,2,5 union all
select '2009-2',4,2,4,0 union all
select '2009-3',2,0,0,7
--------------开始查询--------------------------
select
(SELECT AVG(A) FROM tb WHERE A<>0) as [AVG(a)],
(SELECT AVG(B) FROM tb WHERE B<>0) as [AVG(b)],
(SELECT AVG(C) FROM tb WHERE C<>0) as [AVG(c)],
(SELECT AVG(D) FROM tb WHERE D<>0) as [AVG(d)]----------------结果----------------------------
/* AVG(a) AVG(b) AVG(c) AVG(d)
----------- ----------- ----------- -----------
3 2 3 6(1 行受影响)
*/