求sql语句高手请进，（祝各位新年快乐）

[xiaojinchao]

这样的语句能实现吗
表 tab
name a1 a2
a 13 8
a 20 20
b 0 5
b 40 20
求出这样的结果
name 总记录数 a1>0的数 a1>0并且a2>10
a 2 2 1
b 2 1 1

我这样的，select name,count(name),sum(decoude(sign(a1),1,1))a from tab group by name

这样能求出前两个条件，但后面那个并且的无法求出，，高手帮忙啊？？

[xiaoxiao1984]

select name,count(name),sum(decode(sign(a1),1,1,0)),sum(decode(sign(a1),1,decode(sign(a2-10),1,1,0),0)) from tab group by name

呵呵，寻漏掉一列了

[honghong118cn]

select name,count(name),
sum(case when a1>0 then 1 else o end )a1,
sum(case when a1>0 and a2>10 then 1 else 0 end)a2
from tab group by name

[xiaojinchao]

虽然分数这么少，大家也回答，谢谢啊

[xiaofangzyl]

你们写的结果都能出来, 但我不理解怎么是这样写,case when a1>0 then 1 else o end 是什么意思,还有decode(sign(a1),1,decode(sign(a2-10),1,1)是什么意思,能解释一下吗?

[xiaofangzyl]

你们这是咱想出来的,说说思路,好吗?

[xiaojinchao]

怎么没人回答啊，，，高手们拜托啊

[沝林]

select name,count(name),sum(decode(sign(a1),1,decode(sign(a2-10),1,1)))a from tab group by name

9i 下可以这样：
select name,count(name),sum(case when a1>0 then 1 end) a , sum(case when a1>0 and a2 >10 then 1 end) from tab group by name

[Gundam_zero]

up