id, name

shandong01 name1

shandong02 name2

shandong03 name3

beijing01 name4

beijing02 name5

beijing03 name6

shanghai01 name7

复制代码 代码如下:

select id,name

from table

order by case when id like 'shanghai%' then 0 when id like 'beijing%' then 1 else 2 end

result:

shanghai01 name7

beijing01 name4

beijing02 name5

beijing03 name6

shandong01 name1

shandong02 name2

shandong03 name3