Oracle 使用sign函数分组小记

        在http://www.itpub.net/thread-1834035-1-1.html看到一个使用sign分组的用法，个人觉得不错，记录下：

       问题：
       想知道满足下面条件的
        sale > 2000 and age > 22,
        sale > 2000 and age < 22,
        sale < 2000 and age > 22,
        sale < 2000 and age < 22
      数量及sale总和：

      普通写法：使用四个union all,具体的自己写，类似下面sql： 

     

with tmp_t as(
select 1 as v_id,3000 as sale,22 as age from dual union all
select 2,2100,23 from dual union all
select 3,2300,21 from dual union all
select 4,1000,19 from dual union all
select 5,1000,23 from dual union all
select 6,2600,25 from dual union all
select 7,2200,18 from dual union all
select 8,1500,20 from dual union all
select 9,1600,35 from dual union all
select 10,2000,22 from dual union all
select 11,2000,21 from dual 
)
select sum(case when sale<2000 and age<22 then 1 else 0 end) as 小于2000且age小于22,
       sum(case when sale<2000 and age<22 then sale else 0 end) as sale总和,
       sum(case when sale<2000 and age>22 then 1 else 0 end) as 小于2000且age大于22,
       sum(case when sale<2000 and age>22 then sale else 0 end) as sale总和,
       sum(case when sale>2000 and age<22 then 1 else 0 end) as 大于2000且age小于22,
       sum(case when sale>2000 and age<22 then sale else 0 end) as sale总和,
       sum(case when sale>2000 and age>22 then 1 else 0 end) as 大于2000且age大于22,
       sum(case when sale>2000 and age>22 then sale else 0 end) as sale总和
from tmp_t

     结果如下：

     

      使用sign函数sql如下：

     

with tmp_t as(
select 1 as v_id,3000 as sale,22 as age from dual union all
select 2,2100,23 from dual union all
select 3,2300,21 from dual union all
select 4,1000,19 from dual union all
select 5,1000,23 from dual union all
select 6,2600,25 from dual union all
select 7,2200,18 from dual union all
select 8,1500,20 from dual union all
select 9,1600,35 from dual union all
select 10,2000,22 from dual union all
select 11,2000,21 from dual 
)
select decode(sign(sale-2000),1,'大于2000',-1,'小于2000'),decode(sign(age-22),1,'大于22',-1,'小于22'),count(1),sum(sale)
from tmp_t
group by sign(sale-2000),sign(age-22)
having sign(sale-2000)*sign(age-22)<>0
order by sign(sale-2000),sign(age-22)

    结果如下：
   

      全文完。