oracle中sql的case语句运用--根据不同条件去排序！-白红宇

oracle中sql的case语句运用--根据不同条件去排序！

阅读量：799 次

发布时间：2023-02-25

本文共 1838 字，大约阅读时间需要 6 分钟。

CASE???PLSQL???????

?PLSQL????CASE?????????????????????????????????????????CASE?????????????????????

1. CASE???????

CASE??????????

CASE    WHEN condition THEN        return_value    WHEN comexp THEN        returnvalue    ELSE        returnvalueEND

???condition????????????return_value????????????comexp?????????????????????

2. CASE???????

???????CASE????????????????????????????

SELECT cust_last_name,       CASE           WHEN credit_limit = 100 THEN 'low'           WHEN credit_limit = 5000 THEN 'high'           ELSE 'medium'       ENDFROM customers;

???????????????credit_limit???????????'low'?'high'?'medium'???????????????????????????

3. CASE???????

???????????????????CASE??????????????

CASE    WHEN value ELSE NULLEND AS result_columnFROM dual;

???CASE?????????????????????

SELECT CASE(    SELECT count(*) as s1 FROM t1 where a = 1) as s1_result,       CASE(           SELECT count(*) as s2 FROM t1, t2 where t1.a = t2.a and t2.a = 1       ) as s2_resultFROM dual;

??????????????????CASE??????????CASE????????

4. ??????????

????????????????????????????????????????????

SELECT JCXX.ID,       JCXX.FXSJ,       JCXX.ZT,       JCXX.XGSJFROM T_JCXX JCXXJOIN T_ZDRYBK_XQ ZDRYON JCXX.ID = ZDRY.JCXX_IDJOIN T_JBXX_ZDRYK RYKON RYK.RYJBXX_ID = ZDRY.ZDRY_IDWHERE JCXX.SCBS = '0'   AND JCXX.SFXXYW = '0'ORDER BY JCXX.ZT DESC,       JCXX.ID DESCCASE    WHEN JCXX.ZT = '0' THEN JCXX.FXSJ    ELSE JCXX.XGSJEND DESCNULLS LAST,JCXX.ID DESC NULLS LAST;

????????????????????JCXX.ZT???????CASE??????ZT???????????FXSJ?XGSJ?????????????