求 SQL 语句一条

Question

表中有如下字段：
NAME    VALUE1      VALUE2
A         3            2
A         4            7
A         1            1
A         2            5
B         6            4
B         3            5想分类统计 VALUE2-VALUE1 差额 （<0 =0 >0 三种），得到如下表：NAME     <0      =0      >0
A        1       1       2
B        1       0       1该如何实现,谢谢.

Answer 1

select name,
       count(decode(sign(value2-value1),-1,1)) "<0",
       count(decode(sign(value2-value1),0,1)) "=0",
       count(decode(sign(value2-value1),1,1)) ">0"
from tt
group by name