sql 自定义排序

城市排序，按照规定的城市进行排序

方法一：利用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

image.png

方法二：利用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)

image.png

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