这样的SQL如何写(200分)

某表例子如下：
a1 a2 a3 a4 a5
--------------------
k1 0 10 10 10
k1 10 0 10 10
k1 5 5 10 10
k2 10 0 10 10
k3 0 10 10 10
select a1,avp(a2/a4) as av1,avp(a3/a5) as av2,sum(a4) as su1,sum(a5) as su2 from temp group a1
要求改改此查询，使得av1要滤掉a2是0后求平均，av2要滤掉a3是0后求平均，请大家看看如何改。
最好给出一句select。

补充：
select a1,avp(a2/a4) as av1,avp(a3/a5) as av2,sum(a4) as su1,sum(a5) as su2 from temp group a1
的结果为：
a1 av1 av2 su1 su2
---------------------------------------------------------------
k1 (0/10+10/10+5/10)/3=0.5 (10/10+5/10+0/10)/3=0.5 30 30
k2 1 0 10 10
k3 0 1 10 10
改为
a1 av1 av2 su1 su2
---------------------------------------------------------------
k1 (0/10+10/10+5/10)/2=0.75 (10/10+5/10+0/10)/2=0.75 30 30
k2 1 0 10 10
k3 0 1 10 10

只能用两个Query了。
select a1,avp(a2/a4) as av1,sum(a4) as su1 from temp group a1 where a2>0
select a1,avp(a3/a5) as av2,sum(a5) as su2 from temp group a1 where a3>0

to barton:
错，请看好提问内容说明及例子

提前

我顶

着急。开来拿分呀，我再顶

建议：
1。使用视图
2。使用如下语法
select .... from
(select .，b，.. from ... where ) as tmp1
where tmp1.b<>....
可以嵌套很多层
3。使用union

to zzz^^^:
1。不想用视图
2。请具体
3。union能实现么

难道真的实现不了！？
我再提前

在 Mssql 中
create table #temp(a1 char(2),a2 float,a3 float,a4 float,a5 float)
insert #temp values('k1', 0,10,10,10)
insert #temp values('k1',10, 0,10,10)
insert #temp values('k1', 5, 5,10,10)
insert #temp values('k2',10, 0,10,10)
insert #temp values('k3', 0,10,10,10)

select a1,isnull(avg(case when a2=0 then null else a2/a4 end),0) as av1,
isnull(avg(case when a3=0 then null else a3/a5 end),0) as av2,
sum(a4) as su1,sum(a5) as su2 from #temp group by a1
或者
select a1,isnull(avg(nullif(a2/a4,0)),0) as av1,
isnull(avg(nullif(a3/a5,0)),0) as av2,sum(a4) as su1,
sum(a5) as su2 from #temp group by a1

非常感谢xuechao，虽然你的答案我不能运行（access库），但在你的启发下，我用更简单一点的
已搞定
select a1,avg(iif(a4=0,null,a2/a4)) as av1,avp(iif(a5=0,null,a3/a5)) as av2,sum(a4) as su1,sum(a5) as su2 from temp group a1

分数给你了

接受答案了.