mysql表的排序 ORDER BY field1 [ASC [DESC]], [field2…] [ASC [DESC]],…
这里是想让它以 num 排序 但是 num=-1的那条数据单独为最大值
法一:field( field1,val1,val2,…)
这里大小顺序是 … >val2 >val1 >field1(其他的数据) 子后向前=》从大到小
order by field 时 Using filesort(一种速度比较慢的外部排序) , 一般不建议使用
select * from test order by field(num,-1) asc,num asc;
法二:case when … then … when … then … else … end
select * from test order by case when num = -1 then 1 else 0 end desc;
数据分为2个部分:
我认为 表中的数据分为两部分, num=-1的一条拿出来作为最大值(特殊部分),然后其他的作为一个整体(其他部分) 然后2个部分进行排序,再用 进行一次排序可达到想要的效果
方法中的2个例子结果是一样的,但是想对来说第二种用出多些,因为它可以自由选择大、小的作为特殊部分
#表 test 共有2个字段 id,name
mysql> desc test;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| num | int(11) | NO | | 0 | |
+-------+------------------+------+-----+---------+----------------+
mysql> select * from test;
+----+-----+
| id | num |
+----+-----+
| 1 | -1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
+----+-----+
#两种方法获取到相同的排序 num=-1 作为最大值排序
mysql> select * from test order by field(num,-1) desc;
mysql> select * from test order by case when num = -1 then 1 else 0 end desc;
+----+-----+
| id | num |
+----+-----+
| 1 | -1 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
+----+-----+
mysql> select * from test order by field(num,-1) asc;
mysql> select * from test order by case when num = -1 then 1 else 0 end asc;
+----+-----+
| id | num |
+----+-----+
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
| 1 | -1 |
+----+-----+
#当进行二次排序时,得到了想要的结果
mysql> select * from test order by field(num,-1) desc,num desc;
mysql> select * from test order by case when num = -1 then 1 else 0 end desc, num desc;
+----+-----+
| id | num |
+----+-----+
| 1 | -1 |
| 7 | 5 |
| 6 | 4 |
| 5 | 3 |
| 4 | 2 |
| 3 | 1 |
| 2 | 0 |
+----+-----+
mysql> select * from test order by field(num,-1) asc,num asc;
mysql> select * from test order by case when num = -1 then 1 else 0 end asc, num asc;
+----+-----+
| id | num |
+----+-----+
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
| 1 | -1 |
+----+-----+
#这里是 num=2 作为最大值进行倒序排序 所以num=2在最前面
mysql> select * from test order by case when num = 2 then 1 else 0 end desc;
+----+-----+
| id | num |
+----+-----+
| 4 | 2 |
| 1 | -1 |
| 2 | 0 |
| 3 | 1 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
+----+-----+
# 这里是 num=2 作为最小值进行正序排序 所以num=2在最前面
mysql> select * from test order by case when num = 2 then 0 else 1 end;
+----+-----+
| id | num |
+----+-----+
| 4 | 2 |
| 1 | -1 |
| 2 | 0 |
| 3 | 1 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
+----+-----+