select a.id,
a.name,
ac.name AS categoryName,
a.province ,
a.city,
a.legal_representative AS legalRepresentative,
a.registration_date AS registrationDate
from
xxxx_xxxxx a
left join
xxxxxxxxx_cccccccc ac on a.category_id = ac.id
where a.audit_status = 1
order by
if (a.id in (select ast.association_id from ccccc_cccccc ast where ast.status =1 order by ast.sort_num asc) ,0,1),
case
when a.city ='深圳市' then 0
when a.city ='广州市' then 1
when a.province ='北京' then 2
when a.province ='上海' then 3
when a.city ='杭州市' then 4
when a.city ='武汉市' then 5
when a.city ='成都市' then 6
when a.province ='重庆' then 7
when a.city ='南京市' then 8
when a.province ='天津' then 9
when a.city ='苏州市' then 10
when a.city ='西安市' then 11
when a.city ='长沙市' then 12
when a.city ='沈阳市' then 13
when a.city ='青岛市' then 14
when a.city ='郑州市' then 15
when a.city ='大连市' then 16
when a.city ='东莞市' then 17
else 18
end,
CONVERT( a.province USING gbk ) COLLATE gbk_chinese_ci asc,
CONVERT( a.city USING gbk ) COLLATE gbk_chinese_ci asc
mysql 按特定城市优先排序,然后按省份的中文字母排序,然后按城市的中文字母排序 示例。
于 2023-12-13 10:57:08 首次发布