ÇósqlÓï¾ä

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

---

ÄǵÚÒ»¸öÓ¦¸Ã²»ÊÇ5°É

---

SQL code:
create table tb(Date nvarchar(10),a int,b int,c int ,d int)
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