oracle 的sign函数,Oracle 使用sign函数分组小记

在外链网址已屏蔽看到一个使用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)

结果如下：

全文完。