Oracle 安指定字段指定值 排序

思路 ： 对需要排序的字段值进行再处理

 

1、　利用DECODE 函数

 SELECT
          A.ACTIVITY_PERSON_ID,
          A.ACTIVITY_DEPT_NAME,
          A.ACTIVITY_OFFICE_NAME,
          A.CREATE_TIME,  
          A.STATE
         
        FROM
          MARKET_ACTIVITY A
        ORDER BY 
        DECODE(A.STATE ,'1',-2,'3',-1,A.STATE ) 

2、利用 CASE子句

　　

SELECT A.ACTIVITY_PERSON_ID,
       A.ACTIVITY_DEPT_NAME,
       A.ACTIVITY_OFFICE_NAME,
       A.CREATE_TIME,
       A.STATE

  FROM MARKET_ACTIVITY A
  
 ORDER BY CASE A.STATE
            WHEN '1' THEN -2
            WHEN '3' THEN  -1
            ELSE
             TO_NUMBER(NVL(A.STATE, 0))
          END