ORDER BY FIELD ，给定的字段顺序最后出现 [英] ORDER BY FIELD , given field order comes at last

问题描述

我有如下表业务"

我想以自定义顺序按类型获取记录顺序.所以我写了这样的查询SELECT * FROM business按字段排序(类型，'type3'，'type2'，'type10')

I want to fetch the record order by type in custom order. So I wrote the query like SELECT * FROM business ORDER BY FIELD (type, 'type3', 'type2', 'type10')

但是发生的情况是其他类型按顺序出现，并且给定的顺序最终变为.给定的顺序应该在顶部，然后是其他记录.以上查询返回结果如下.

but what happen is other types comes up in order and the given order becomes at last. The given order should be at top and then other records. Above query returns the result as bellow.

如何将 type3、type2 和 type10 置于顶部.

How to bring the type3, type2 and type10 at top in order.

推荐答案

这样:

SELECT 
  * 
FROM 
  business 
ORDER BY 
  `type` IN ('type3', 'type2', 'type10'),
  FIELD (`type`, 'type3', 'type2', 'type10')

这篇关于ORDER BY FIELD ，给定的字段顺序最后出现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！