[Oracle/Sql] Decode与Case

Decode和case都可以实现SQL中的条件结构，下面为用法示例：

select id,name,score,decode(floor(score/20),5,'A',4,'B',3,'C',2,'D','E') as grade from tb_score01 order by grade

运行情况：

SQL> select id,name,score,decode(floor(score/20),5,'A',4,'B',3,'C',2,'D','E') as grade from tb_score01 order by grade;

        ID NAME                      SCORE G
---------- -------------------- ---------- -
         3 VJJYYVKXKJKZADERKU           84 B
        11 AGUNCTGZFGDJJGLQTQMY         81 B
         2 YUVFJNTQAYJBTDYAQNIB         50 D
        10 XDJFPNUIP                    53 D
         9 ZFJKTW                       59 D
         1 KBKVMELHCIFEWFI              42 D
         5 DUEEXRBJJI                    5 E
         8 TEEGBSRVHASOTBHN             30 E
         7 UAMTSMFDE                     2 E
        13 YUNHCQSBVPQ                  27 E
        14 VIDHHKOAWOTQXRBSI            14 E

        ID NAME                      SCORE G
---------- -------------------- ---------- -
         4 KBJWREHVF                     8 E
        15 RMWEROFCFEVVFRU              14 E
         6 QLLJNGXVFR                    2 E
        12 WNMWJP                       30 E

已选择15行。

 

Case示例：

select id,name,score,case floor(score/20) when 5 then 'A' when 4 then 'B' when 3 then 'C' when 2 then 'D' else 'E' end as grade from tb_score01 order by grade 

执行情况：

SQL> select id,name,score,case floor(score/20) when 5 then 'A' when 4 then 'B' when 3 then 'C' when 2 then 'D' else 'E' end as grade from tb_score01 order by grade ;

        ID NAME                      SCORE G
---------- -------------------- ---------- -
         3 VJJYYVKXKJKZADERKU           84 B
        11 AGUNCTGZFGDJJGLQTQMY         81 B
         2 YUVFJNTQAYJBTDYAQNIB         50 D
        10 XDJFPNUIP                    53 D
         9 ZFJKTW                       59 D
         1 KBKVMELHCIFEWFI              42 D
         5 DUEEXRBJJI                    5 E
         8 TEEGBSRVHASOTBHN             30 E
         7 UAMTSMFDE                     2 E
        13 YUNHCQSBVPQ                  27 E
        14 VIDHHKOAWOTQXRBSI            14 E

        ID NAME                      SCORE G
---------- -------------------- ---------- -
         4 KBJWREHVF                     8 E
        15 RMWEROFCFEVVFRU              14 E
         6 QLLJNGXVFR                    2 E
        12 WNMWJP                       30 E

已选择15行。

 

表及充值语句如下：

CREATE TABLE tb_score01
(
    id NUMBER not null primary key,
    name varchar2(20),
    score int
)

Insert into tb_score01
select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(0,100) from dual
connect by level<=15
order by dbms_random.random

2020-01-27