27,579
社区成员
发帖
与我相关
我的任务
分享
create table u01
(Name varchar(10),num1 int,num2 int,num3 int)
insert into u01
select '张三',2,2,2 union all
select '李四',4,4,4 union all
select '王二',6,6,6 union all
select '老大',0,4,12
with t as
(select Name,num1,num2,num3,
(num1+num2+num3)/
(case when num1<>0 then 1 else 0 end+
case when num2<>0 then 1 else 0 end+
case when num3<>0 then 1 else 0 end) 'AVG_Y'
from u01)
select Name,num1,num2,num3,AVG_Y from t
union all
select 'AVG_X',
sum(num1)/sum(case when num1<>0 then 1 else 0 end),
sum(num2)/sum(case when num2<>0 then 1 else 0 end),
sum(num3)/sum(case when num3<>0 then 1 else 0 end),
cast((sum(num1)/sum(case when num1<>0 then 1 else 0 end)+
sum(num2)/sum(case when num2<>0 then 1 else 0 end)+
sum(num3)/sum(case when num3<>0 then 1 else 0 end))/3.0 as decimal(5,1))
from t
/*
Name num1 num2 num3 AVG_Y
---------- ----------- ----------- ----------- ---------
张三 2 2 2 2.0
李四 4 4 4 4.0
王二 6 6 6 6.0
老大 0 4 12 8.0
AVG_X 4 4 6 4.7
(5 row(s) affected)
*/