oracle查询排序id,Oracle查询数据怎么按照甲乙丙丁排序？谢谢

满意答案

davidtanfei

2013.11.24

采纳率：59%    等级：12

已帮助：7714人

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;

00分享举报