說說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. 根據(jù)表達式排序 #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 |+----+----+----+-----+小結