已知一张表table,内容如下所示:
字段1 字段2 字段3
5 null 3
null 8 7
3 2 6
2 null null想得到如下的结果:
3.33 5 5.33说明:
3.33=(5+3+2)/3
5=(8+2)/2
5.33=(3+7+6)/3
字段1 字段2 字段3
5 null 3
null 8 7
3 2 6
2 null null想得到如下的结果:
3.33 5 5.33说明:
3.33=(5+3+2)/3
5=(8+2)/2
5.33=(3+7+6)/3
调试欢乐多
INSERT TB
SELECT 5, null, 3 UNION ALL
SELECT null, 8, 7 UNION ALL
SELECT 3, 2, 6 UNION ALL
SELECT 2, null, nullSELECT SUM([字段1])/COUNT([字段1]),
SUM([字段2])/COUNT([字段2]),
SUM([字段3])/COUNT([字段3])
FROM TB DROP TABLE TB
/*
3.333333 5.000000 5.333333
*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-09 14:15:45
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([字段1] int,[字段2] int,[字段3] int)
insert [tb]
select 5,null,3 union all
select null,8,7 union all
select 3,2,6 union all
select 2,null,null
--------------开始查询--------------------------
select avg(字段1),avg(字段2),avg(字段3) from tb
----------------结果----------------------------
/*
----------- ----------- -----------
3 5 5
*/
create table #a(a int,b int,c int)
insert into #a(a,b,c)
select 5,null,3 union all
select null,8,7 union all
select 3,2,6 union all
select 2 ,null,null
select Convert(float,sum(a))/Convert(float,count(a)),Convert(float,sum(b))/Convert(float,count(b)),
Convert(float,sum(c))/Convert(float,count(c)) from #a
AVG 不符合楼主要求!
刚在网上看到下面的语句好像也是可以实现的:
select sum(isnull(字段1,0))/sum(case when isnull(字段1,0)=0 then 0 else 1 end) ,
sum(isnull(字段2,0))/sum(case when isnull(字段2,0)=0 then 0 else 1 end) ,
sum(isnull(字段3,0))/sum(case when isnull(字段3,0)=0 then 0 else 1 end)
from table