orcale按照指定字段指定值排序

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