FIELD函数实现字段值按特定要求排序以及可能会出现的问题

平常在写查询SQL的时候，依赖“SELECT * FROM tableA  ORDER BY   column1 DESC,column2 ASC "这种排序方式就可以解决很大一部分的排序问题了。但有时候业务上需要展示特定文案的顺序时，这种直接进行正序或者倒序的排列方式就不能满足要求了。

举个例子，浙江省政府目前对全省各市的排列是”杭宁温湖嘉绍金衢舟台丽“，这时该怎么合理设计返回正确顺序的数据呢？

1、表结构简单，数据量少容易维护，仅用来展示没有其他复杂交互时，可以人为的按照”杭宁温湖嘉绍金衢舟台丽“这样的顺序插入待展示的数据。

如下图所示：从区县级别(type=3)的排序、市级别(type=2)的排序都是按照给定的要求来插入数据的，那么在查询的时候可以查询，不用再做任何操作了

2、在表结构复杂，数据量多的情况下，上面的做法就不适用了，此时为了能够按照要求排序，可以使用FIELD函数来实现功能。

SELECT DISTINCT
	city
FROM
	ent_data
ORDER BY
	FIELD(city,'杭州市','宁波市','温州市','湖州市','嘉兴市','绍兴市','金华市','衢州市','舟山市','台州市','丽水市')

执行结果如下：

从执行结果来看，city字段确实按照杭宁温排序了，但是排在第一个值的不是“杭州市”，而是“浙江省”。这个问题就需要具体介绍一下FIELD函数的功能和含义了。

FIELD函数
①功能：用来对SQL中查询结果集进行指定顺序排序

②使用格式：order by field（str，str1，str2，str3，str4……）

str指的是字段名字，字段str按照字符串str1，str2，str3，str4的顺序（str值相同时按照主键的顺序）返回查询到的结果集。

如果表中存在某些str字段值不为str1，str2，str3，str4时，这些值所对应的表中记录会放在已知顺序的结果集的最前面返回。

用上图来具体解释这句话的含义就是：“浙江省” 这个city字段所记录的值，并不在“杭宁温湖嘉绍金衢舟台丽”这11个值之中，那么程序会把 city='浙江省' 的数据放到“杭宁温”的数据之前展示。

 

FIELD函数更多对比介绍可参考这篇博文

---

2020/09/17更新 

order by 和分页一起使用时，大多数情况下用order by排序配合分页的时候，是没有问题的

但是在涉及到自定义排序的时候，就有可能会出现分页时有的数据重复，有的数据遗漏的现象（这个问题容易被忽视）

解决这个问题，可以 在自定义排序后，再添加一个value值唯一的字段进行辅助排序（如：id），这样就可以限制排序的唯一性，从而就不会出现分页时数据重复出现，或者缺失的问题了。

SELECT *
FROM table_name
ORDER BY FIELD(COLUMN_NAME,2,1,3),id