一个栗子

订单表结构如下:

1
2
3
4
5
6
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(60) NOT NULL COMMENT '订单标题',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '订单状态:1 待支付、2 支付中、3 已支付',
PRIMARY KEY (`id`)
);

测试数据:

1
2
3
4
INSERT INTO `orders` VALUES (1, '订单1', 3);
INSERT INTO `orders` VALUES (2, '订单2', 1);
INSERT INTO `orders` VALUES (3, '订单3', 2);
INSERT INTO `orders` VALUES (4, '订单4', 1);

在展示订单列表的时候,要求将状态为支付中的展示在最前面,然后是状态为待支付、已支付。

一般的需求按照订单状态大小进行排序可以直接使用 ORDER BY 订单状态 完成,比如 待支付(1)、支付中(2)、已完成(3) 或者 已完成(3)、支付中(2)、待支付(1)

但是像上面这种需求,并不完全按照订单状态大小,而是按照指定的订单状态顺序 支付中(2)、待支付(1)、已完成(3) 来进行排序,当遇到这种情况就可以使用 FIELD() 函数了。

FEILD() 函数说明

函数结构:FIELD(s, s1, s2, s3...)

作用:返回第一个字符串 s 在字符串列表(s1, s2, s3…)中的位置。

简单示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT FIELD('a', 'a', 'b', 'c', 'd');

+--------------------------------+
| FIELD('a', 'a', 'b', 'c', 'd') |
+--------------------------------+
| 1 |
+--------------------------------+

SELECT FIELD('c', 'a', 'b', 'c', 'd');

+--------------------------------+
| FIELD('c', 'a', 'b', 'c', 'd') |
+--------------------------------+
| 3 |
+--------------------------------+

使用 FIELD() 函数

在使用 FIELD() 函数进行排序的时候,只需要将状态按照上面要求的顺序进行排列,就可以实现了。

1
2
3
4
5
6
7
8
9
10
SELECT * FROM orders ORDER BY FIELD(`status`, 2, 1, 3);

+----+---------+--------+
| id | title | status |
+----+---------+--------+
| 3 | 订单3 | 2 |
| 2 | 订单2 | 1 |
| 4 | 订单4 | 1 |
| 1 | 订单1 | 3 |
+----+---------+--------+

FIELD() 函数的返回值一起打印出来。

1
2
3
4
5
6
7
8
9
10
SELECT *, FIELD(`status`, 2, 1, 3) FROM orders ORDER BY FIELD(`status`, 2, 1, 3);

+----+---------+--------+--------------------------+
| id | title | status | FIELD(`status`, 2, 1, 3) |
+----+---------+--------+--------------------------+
| 3 | 订单3 | 2 | 1 |
| 2 | 订单2 | 1 | 2 |
| 4 | 订单4 | 1 | 2 |
| 1 | 订单1 | 3 | 3 |
+----+---------+--------+--------------------------+

可以看到,实际上在查询的时候 ORDER BY 的是 FEILD() 函数的返回值,也就是 status 的值在 (2, 1, 3) 中的位置。

一句话总结,当遇到自定义排序规则时就可以使用 FEILD() 函数。