大家好，帮我看看这个关于decode的语句

Question

大家好，我想用一个decode语句去统计里个数值的总个数
这个数值为(trs_time-tran_date)*24
如果这个数值小于2，那么把统计的总个数存入 lessthan2 列中
如果这个数值大于2，那么把统计的总个数存入 greaterthan2 列中，请问这个语句怎么写啊？我想的是用
select count(decode((trs_time-tran_date)*24,<=2,1))"lessthan2",
       count(decode((trs_time-tran_date)*24,>2,1))"greaterthan2"
from   alm_inf
可是这样出不来啊，大家帮帮我啊

Answer 1

select count(decode(sign(2-(trs_time-tran_date)*24), 1,1))"lessthan2", 
      countdecode(sign(2-(trs_time-tran_date)*24), -1,1)) "greaterthan2" 
from  alm_inf 

Answer 2

decode改成case
count(decode((trs_time-tran_date)*24, <=2,1))-->
count(case when (trs_time-tran_date)*24<=2 then 1 end)

Answer 3

select count(decode(sign(2-(trs_time-tran_date)*24), 1,1)) "lessthan2", 
       count(decode(sign(2-(trs_time-tran_date)*24), 0,1)) "equal",
      countdecode(sign(2-(trs_time-tran_date)*24), -1,1)) "greaterthan2" 
from  alm_inf 

Answer 4

decode的第一个里面好像不能用 >/<等比较符号的，这种情况下需要用case when:
select count(case when (trs_time-tran_date)*24 <=2 then "lessthan2" else "greaterthan2" end) 
from  alm_inf