目录
一、准备数据
#创建用户表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(60) NOT NULL COMMENT '姓名',
`age` int(11) NOT NULL COMMENT '年龄',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
#插入数据
INSERT INTO user (id, name, age) VALUES (1, '小明', 20);
INSERT INTO user (id, name, age) VALUES (2, '小红', 19);
INSERT INTO user (id, name, age) VALUES (3, '小蓝', 20);
二、函数FIELD介绍
语法:SELECT FIELD(value, val1, val2, ...)
描述:用于返回指定值在给定值列表中的索引的位置,value是给指定要校验的值,val1, val2, ...是定值列表。当value不存在给定值列表中则返回0
三、排序操作
语法:SELECT 字段 FROM 表名称 ORDER BY FIELD(value, val1, val2, ...) ASC|DESC
描述:value是指定排序的字段,val1, val2, ...是需要排序的值
1.指定所有值排序
①.正序排序
SELECT * from user order by FIELD(name,"小红","小蓝", "小明");
②.逆序排序
SELECT * from user order by FIELD(name,"小红","小蓝", "小明") desc;
2.指定部分值排序
①.正序排序
SELECT * from user order by FIELD(name,"小红","小明");
②.逆序排序
SELECT * from user order by FIELD(name,"小红","小明") desc;