create table test1
(
id int,
name char(20),
rank varchar2(2),
primary key (id)
);
select * from test1;
insert into test1(id,name,rank) values(1,'aa','甲');
insert into test1(id,name,rank) values(2,'bb','乙');
insert into test1(id,name,rank) values(3,'dd','丁');
insert into test1(id,name,rank) values(4,'cc','丙');
insert into test1(id,name,rank) values(5,'ee','乙');
--甲、乙、丙、丁、戊、己、庚、辛、壬、癸
select *
from test1
order by (
case
when rank='甲' then 1
when rank='乙' then 2
when rank='丙' then 3
when rank='丁' then 4
when rank='戊' then 5
when rank='己' then 6
when rank='庚' then 7
when rank='辛' then 8
when rank='壬' then 9
when rank='癸' then 10
else 11
end
) asc,name asc;
select *
from test1
order by decode(rank,
'甲',1,
'乙',2,
'丙',3,
'丁',4,
'戊',5,
'己',6,
'庚',7,
'辛',8,
'壬',9,
'癸',10,
11
) asc,name asc;
