可以做到
用下面的语句
select sum(茄子)/sum(case ISNULL(茄子,0) when 0 then 0 else 1 end),
sum(白菜)/sum(case ISNULL(白菜,0) when 0 then 0 else 1 end),
sum(萝卜)/sum(case ISNULL(萝卜,0) when 0 then 0 else 1 end),
sum(樱桃)/sum(case ISNULL(樱桃,0) when 0 then 0 else 1 end),
from 表
用下面的语句
select sum(茄子)/sum(case ISNULL(茄子,0) when 0 then 0 else 1 end),
sum(白菜)/sum(case ISNULL(白菜,0) when 0 then 0 else 1 end),
sum(萝卜)/sum(case ISNULL(萝卜,0) when 0 then 0 else 1 end),
sum(樱桃)/sum(case ISNULL(樱桃,0) when 0 then 0 else 1 end),
from 表
sum(白菜)/sum(case ISNULL(白菜,0) when 0 then 0 else 1 end),
sum(萝卜)/sum(case ISNULL(萝卜,0) when 0 then 0 else 1 end),
sum(樱桃)/sum(case ISNULL(樱桃,0) when 0 then 0 else 1 end),
from 表
(case when sum(白菜) = 0 then 0 else sum(白菜)/sum(case when 白菜 = 0 then 0 else 1 end) end) as 白菜,
(case when sum(萝卜) = 0 then 0 else sum(萝卜)/sum(case when 萝卜 = 0 then 0 else 1 end) end) as 萝卜,
(case when sum(樱桃) = 0 then 0 else sum(樱桃)/sum(case when 樱桃 = 0 then 0 else 1 end) end) as 樱桃
from 表
where (价格<>0) and not (价格 is null)
,select avg(白菜) 白菜 from t where 白菜<>0
,select avg(萝卜) 萝卜 from t where 萝卜<>0
,select avg(樱桃) 樱桃 from t where 樱桃<>0
)
where 价格<>0 and 价格 is not null
select * from (select avg(茄子) 茄子 from t where 茄子<>0) a,
,(select avg(白菜) 白菜 from t where 白菜<>0) b,
,(select avg(萝卜) 萝卜 from t where 萝卜<>0) c,
,(select avg(樱桃) 樱桃 from t where 樱桃<>0) d