如何用“优、良、中、差”来排序(100分)

H

hehuan

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #1

如题，order by 只能是字母排序，但我想用某种特定的顺序来排序，该如何写？非常感谢！

 

水

水影8888

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #2

加一个辅助字段是最方便的,如:为优时辅助字段值为A,以此类推,排序时按辅助字段排就行了.
或者:数据库中根本就不存储优良中差,而是存储ABCD,显示的时候调整为优良中差
这是笨办法,看高手们的吧.

 

D

djrj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #3

--1.可以用楼上的方法，

--2.可以用下面这样(bbb是字段名)
select * from Table1
order by (case bbb
when '优' then 1
when '良' then 2
when '中' then 3
when '差' then 4
else 0
end)

--3.也可以这样(bbb是字段名)
select * from Table1
order by charindex(ltrim(rtrim(bbb)),'优良中差')

 

K

kangshijiu

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #4

方法2比较好,不做别的改动.思路也清晰

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #5

select * from Table1
order by (case bbb
when '优' then 1
when '良' then 2
when '中' then 3
when '差' then 4
else 0
end)

 

S

sefeng1982

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #6

order by + case (另做字段)

 

B

bmsr

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-02

• #7

select * from utable
order by
decode(bbb,'优',1,'良',2,'中',3,'差'4,9999) as sortindex

oracle 数据库下

 

D

dongfangyunxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-03

• #8

select * from table_score
orderby(case
when score>=85 then '优'
when score>=70 then '良'
when score>=60 then '中'
else
'差'
end

 

网

网中戏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-03

• #9

优、良、中、差
拼音不对 Y、L、Z、C
CLYZ 或 ZYLC

 

寻

寻绎

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-11-03

• #10

从前的例子
表：tbl1
season
春
秋 <-----汉字
冬
夏
排序：按“春夏秋冬”自然顺序。
select * from tbl1
order by iif(season='春',1,iif(season='夏',2,iif(season='秋',3,4)))
或
SELECT T1.*
FROM tb1 AS T1, [Select 1 as Idx,'春' as season from (Select Count(*) from tb1) K1
union all
Select 2 as Idx,'夏' as season from (Select Count(*) from tb1) K1
union all
Select 3 as Idx,'秋' as season from (Select Count(*) from tb1) K1
union all
Select 4 as Idx,'冬' as season from (Select Count(*) from tb1) K1
]. AS T
WHERE T1.season=T.season
ORDER BY T.Idx;