sql 自定义排序
2019-06-26 本文已影响0人
AGEGG
城市排序,按照规定的城市进行排序
方法一:利用case when
select `linkageid`,`name`,`parentid` from ibd_province where
(linkageid = 3682 or linkageid = 3671 or linkageid = 3694 or linkageid = 3700 or linkageid = 3787 or linkageid = 3714 or linkageid = 3728 or linkageid = 3721 or linkageid = 3769 )
and `type`=2
ORDER BY (
CASE linkageid
WHEN 3682 THEN 1
WHEN 3671 THEN 2
WHEN 3694 THEN 3
WHEN 3700 THEN 4
WHEN 3787 THEN 5
WHEN 3714 THEN 6
WHEN 3728 THEN 7
WHEN 3721 THEN 8
WHEN 3769 THEN 9
ELSE 999
END ) ASC

方法二:利用FIELD
select `linkageid`,`name`,`parentid` from ibd_province where
(linkageid = 3682 or linkageid = 3671 or linkageid = 3694 or linkageid = 3700 or linkageid = 3787 or linkageid = 3714 or linkageid = 3728 or linkageid = 3721 or linkageid = 3769 )
and `type`=2
ORDER BY FIELD(linkageid,3682,3671,3694,3700,3787,3714,3728,3721,3769)

参考:https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first