说说MySQL ORDER BY

　　在MySQL里，ORDER BY可以有几种玩法mysql文件入库工具？

　　先看下手册里的说明：

　　SELECT [ALL | DISTINCT | DISTINCTROW ].... [ORDER BY {col_name | expr | position} [ASC | DESC], ...]

　　也就是，有三种ORDER BY模式，下面分别简单演示下。

　　测试表：

　　[yejr]@[imysql.com]>show create table t1G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(10) unsigned NOT NULL DEFAULT '0', `c2` int(10) unsigned NOT NULL DEFAULT '0', `c3` int(10) unsigned NOT NULL DEFAULT '0', `c4` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`c1`)mysql文章入库软件, KEY `c2` (`c2`)) ENGINE=InnoDB DEFAULT CHARSET=utf8[yejr]@[imysql.com]>select * from t1;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 0 | 0 | 0 | 0 || 1 | 1 | 1 | 0 || 3 | 3 | 3 | 0 || 4 | 2 | 2 | 0 || 6 | 8 | 5 | 123 || 7 | 6 | 6 | 123 || 10 | 10 | 4 | 123 |+----+----+----+-----+例1. 按指定列名ORDER BY [yejr]@[imysql.com]>select * from t1 order by c2;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 0 | 0 | 0 | 0 || 1 | 1 | 1 | 0 || 4 | 2 | 2 | 0 || 3 | 3 | 3 | 0 || 7 | 6 | 6 | 123 || 6 | 8 | 5 | 123 || 10 | 10 | 4 | 123 |+----+----+----+-----+例2. 按指定序号的列排序 #按第二个列排序（同例1）[yejr]@[imysql.com]>select * from t1 order by 2;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 0 | 0 | 0 | 0 || 1 | 1 | 1 | 0 || 4 | 2 | 2 | 0 || 3 | 3 | 3 | 0 || 7 | 6 | 6 | 123 || 6 | 8 | 5 | 123 || 10 | 10 | 4 | 123 |+----+----+----+-----+#按第三个列排序[yejr]@[imysql.com]>select * from t1 order by 3;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 0 | 0 | 0 | 0 || 1 | 1 | 1 | 0 || 4 | 2 | 2 | 0 || 3 | 3 | 3 | 0 || 10 | 10 | 4 | 123 || 6 | 8 | 5 | 123 || 7 | 6 | 6 | 123 |+----+----+----+-----+例3. 根据表达式排序 #ORDER BY c3=3 DESCmysql文件入库工具 ，也就是如果某条记录c3=3，则它排在第一位#其他非c3=3的记录，则按照聚集索引的顺序显示[yejr]@[imysql.com]>select * from t1 order by c3=3 desc;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 3 | 3 | 3 | 0 || 0 | 0 | 0 | 0 || 1 | 1 | 1 | 0 || 4 | 2 | 2 | 0 || 6 | 8 | 5 | 123 || 7 | 6 | 6 | 123 || 10 | 10 | 4 | 123 |+----+----+----+-----+#甚至还可以用case when#这个例子中MySQL文章入库助手 ，当c3=3时，会被重置成10，其余按照实际值倒序排[yejr]@[imysql.com]>select * from t1 order by case when c3=3 then 10 else c3 end desc;+----+----+----+-----+| c1 | c2 | c3 | c4 |+----+----+----+-----+| 3 | 3 | 3 | 0 || 7 | 6 | 6 | 123 || 6 | 8 | 5 | 123 || 10 | 10 | 4 | 123 || 4 | 2 | 2 | 0 || 1 | 1 | 1 | 0 || 0 | 0 | 0 | 0 |+----+----+----+-----+小结