oracle数据库查询语句指定排序

项目开发中有时候会遇到需要按指定顺序查询的需求,现在开发的项目就遇到这么一个问题,需要显示数据的历史记录,但是历史记录需要按照如G,R,I,Q,H,S等状态排序,使用decode函数排序后发现顺序仍然混乱.

以下是查询混乱的查询方式:

select distinct  seg.projectid,seg.*,batch.*  from t_segment seg left join t_project project on seg.projectID=project.projectID  
inner join t_department department on department.departmentID = project.departmentId  
inner join t_class classa on classa.classID = department.classId  inner join t_file_segment fs on fs.segmentId = seg.segmentID  
inner join (  
select fil.* from t_file fil  inner join t_file_batch fd on fd.fileId = fil.fileID  
inner join t_batch ba on ba.batchID = fd.batchId  where 1=1  and fil.fileType in ('E') and ba.batchType='1' 
) files on fs.fileId = files.fileID  left join t_batch_segment bs on bs.segmentId = seg.segmentID  
left join t_batch batch on batch.batchID = bs.batchId   where 1=1  and batch.batchType = '1'  and bs.segmentType = 0  
and seg.deleteFlag=0 and seg.schApprovalStatus IN  ('G','R','V4','I2','Q2','H','S','I1','Q1','V3','J','O','K','L','M','N','JP','PP','SP','Y','W') 
and seg.projectId in (
  select pro.projectId from t_project pro left join t_segment s on s.projectId=pro.projectId 
  left join t_department dept on dept.departmentID = pro.departmentID where s.schApprovalStatus in ('G','R','V4') 
  or (s.schApprovalStatus in ('G','R','V4','I2','Q2','H','S','I1','Q1','V3','J','O','K','L','M','N','JP','PP','SP','Y','W') 
  and s.plaApprovalStatus in ('G','R','V4'))
) 
order by seg.projectid desc,decode(seg.schApprovalStatus,'G','1','R','2','V4','3','I2','4','Q2','5','H','6','S','7','I1','8','Q1','9','V3','10','J','11','O','12','K','13','L','14','M','15','N','16','JP','17','PP','18','SP','19','Y','20','W','21'),seg.segmentId 

这样查询出来的数据还是不对,因为每个状态后的数字排序时并不按照数字排序,而是按照字符排序,真实排序顺序为'1','11','12','13'......'19','2','20','21'......

以下是正确查询方式:

select distinct  seg.projectid,seg.*,batch.*  from t_segment seg left join t_project project on seg.projectID=project.projectID  
inner join t_department department on department.departmentID = project.departmentId  
inner join t_class classa on classa.classID = department.classId  inner join t_file_segment fs on fs.segmentId = seg.segmentID  
inner join (  
select fil.* from t_file fil  inner join t_file_batch fd on fd.fileId = fil.fileID  
inner join t_batch ba on ba.batchID = fd.batchId  where 1=1  and fil.fileType in ('E') and ba.batchType='1' 
) files on fs.fileId = files.fileID  left join t_batch_segment bs on bs.segmentId = seg.segmentID  
left join t_batch batch on batch.batchID = bs.batchId   where 1=1  and batch.batchType = '1'  and bs.segmentType = 0  
and seg.deleteFlag=0 and seg.schApprovalStatus IN  ('G','R','V4','I2','Q2','H','S','I1','Q1','V3','J','O','K','L','M','N','JP','PP','SP','Y','W') 
and seg.projectId in (
  select pro.projectId from t_project pro left join t_segment s on s.projectId=pro.projectId 
  left join t_department dept on dept.departmentID = pro.departmentID where s.schApprovalStatus in ('G','R','V4') 
  or (s.schApprovalStatus in ('G','R','V4','I2','Q2','H','S','I1','Q1','V3','J','O','K','L','M','N','JP','PP','SP','Y','W') 
  and s.plaApprovalStatus in ('G','R','V4'))
) 
order by seg.projectid desc,decode(seg.schApprovalStatus,'G','01','R','02','V4','03','I2','04','Q2','05','H','06','S','07','I1','08','Q1','09','V3','10','J','11','O','12','K','13','L','14','M','15','N','16','JP','17','PP','18','SP','19','Y','20','W','21'),seg.segmentId 

10以下的数字全部使用'01','02','03'..方式,这种方式查询出来的数据才是正确的.

第一次遇到,网上没看到有此类介绍,自己试出来的,记录下,以便将来查看.