SQL 函数 — case、abs 与 sign

今天一个同事问起SQL语句中如何判断一个结果是正数、负数还是0。如果是正数则返回1，负数和0都返回0。这可以使用case实现，例如：

 

SQL> create table yct(a number);

 

表已创建。

 

SQL> insert into yct values(8);

 

已创建 1 行。

 

SQL> insert into yct values(0);

 

已创建 1 行。

 

SQL> insert into yct values(-5);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from yct;

 

         A
----------
         8
         0
        -5

 

SQL> select case when a>0 then 1 else 0 end
  2    from yct;

 

CASEWHENA>0THEN1ELSE0END
------------------------
                       1
                       0
                       0

 

 

而这个 SQL 可能在 Oracle816 中执行，Oracle816 中并没有 case 关键字，所以做了一下变通，使用 decode 与 abs 函数实现了该功能：

 

SQL> select decode(a, 0, 0,
  2                      decode(a+abs(a), 0, 0, 1)
  3               )
  4  from yct
SQL> /

 

DECODE(A,0,0,DECODE(A+ABS(A),0,0,1))
------------------------------------
                                   1
                                   0
                                   0

 

后来另一个同事找到了 sign 函数，可以更为简洁地实现该功能：

 

SQL> select decode(sign(a), 0, 0,
  2                         -1, 0,
  3                             1)
  4  from yct;

 

DECODE(SIGN(A),0,0,-1,0,1)
--------------------------
                         1
                         0
                         0

 

而进一步地，sign 函数还可以用来判断两个数值的大小。比如比较 yct.a 与 7 的大小，yct.a > 7 则返回10，yct.a < 7 则返回-10，yct.a = 7 则返回 0：

 

SQL> insert into yct values(4);

 

已创建 1 行。

 

SQL> insert into yct values(9);

 

已创建 1 行。

 

SQL> insert into yct values(1);

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from yct;

 

         A
----------
         8
         0
        -5
         4
         9
         1

 

已选择6行。

 

SQL> select decode(sign(a-7), 0, 0,
  2                         -1, -10,
  3                             10)
  4  from yct
SQL> /

 

DECODE(SIGN(A-7),0,0,-1,-10,10)
-------------------------------
                             10
                            -10
                            -10
                            -10
                             10
                            -10

 

已选择6行。

 

下面是 Oracle9201 官方文档中对 sign 函数的介绍：
---------------------------------------------

Syntax

sign::=

Purpose

SIGN returns -1 if n<0, then . If n=0, then the function returns 0. If n>0, then SIGN returns 1.

Examples

The following example indicates that the function's argument (-15) is <0:

SELECT SIGN(-15) "Sign" FROM DUAL;

      Sign
----------
        -1