本文介绍了一种使用SQL来计算每行非零字段的平均值的方法。通过LEFT JOIN和CASE WHEN语句结合使用，实现了对表tbl_avgrow中指定字段(n1, n2, n3, n4)的平均值计算，并考虑了每行不为零的字段数量。
1、表名tbl_avgrow
字段名分别是name,n1,n2,n3,n4
2、sql实现语句
select A.*, (A.n1+A.n2+A.n3+A.n4)/B.count1 as avgrow ,B.count1 as countcalc from tbl_avgrow as A left join
(--每行不为零的字段总数
select name ,A1+A2+A3+A4 as count1 from
(
select name,
--筛选
case when n1=0 then 0 else 1 end A1,
case when n2=0 then 0 else 1 end A2,
case when n3=0 then 0 else 1 end A3,
case when n4=0 then 0 else 1 end A4
from tbl_avgrow
) as counttable
)as B
on A.name =B.name
字段名分别是name,n1,n2,n3,n4
2、sql实现语句
select A.*, (A.n1+A.n2+A.n3+A.n4)/B.count1 as avgrow ,B.count1 as countcalc from tbl_avgrow as A left join
(--每行不为零的字段总数
select name ,A1+A2+A3+A4 as count1 from
(
select name,
--筛选
case when n1=0 then 0 else 1 end A1,
case when n2=0 then 0 else 1 end A2,
case when n3=0 then 0 else 1 end A3,
case when n4=0 then 0 else 1 end A4
from tbl_avgrow
) as counttable
)as B
on A.name =B.name
扫一扫
1412
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
