ORACLE按条件排序的例子

需求: 当天约会的案件在最前面－－> 从未处理过的案件－－> 上次处理时间由小到大排序（即越近催收的案件在越后面）

即： (col_case.case_app_date=sysdate)-->(col_case.case_lst_col_date is null)-->(col_case.case_lst_col_date)

select case when trunc(t.case_app_date)=trunc(sysdate) then '1'
            when t.case_lst_col_date_str is null then '2'
            else '3' end as column1,t.*
from
(
 SELECT  V_COLOVERVIEW_FULL.* FROM V_COLOVERVIEW_FULL WHERE MCOL_ID='zhangq'
OR(AGENT_ID='zhangq' AND AGENT_STATUS='AgentStatus_Agree'
AND AGENT_DATE_FROM<=SYSDATE AND AGENT_DATE_TO>=SYSDATE)
)
t
order by column1,case_lst_col_date_str asc 

 

 博文来源：http://blog.youkuaiyun.com/huanghui22/archive/2006/12/25/1460474.aspx