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