MySQL 排序
假设有 student 表,数据如下:
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 1 | 张三 | 18 |
| 2 | 李四 | 15 |
| 3 | 赵高 | 45 |
| 4 | 孙权 | 34 |
| 5 | 诸葛亮 | 21 |
| 6 | 钱进 | 25 |
| 7 | 宋江 | 29 |
+----+--------+-----+
1. 按年龄从小到大排序
select * from student order by age asc;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 2 | 李四 | 15 |
| 1 | 张三 | 18 |
| 5 | 诸葛亮 | 21 |
| 6 | 钱进 | 25 |
| 7 | 宋江 | 29 |
| 4 | 孙权 | 34 |
| 3 | 赵高 | 45 |
+----+--------+-----+
2. 自定义排序:按姓氏排序,赵钱孙李在前,其他姓氏在后
select * from student order by field(left(name,1), '李', '孙', '钱', '赵') desc;
+----+--------+-----+
| id | name | age |
+----+--------+-----+
| 3 | 赵高 | 45 |
| 6 | 钱进 | 25 |
| 4 | 孙权 | 34 |
| 2 | 李四 | 15 |
| 1 | 张三 | 18 |
| 5 | 诸葛亮 | 21 |
| 7 | 宋江 | 29 |
+----+--------+-----+