mysql order by_说说MySQL 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`), 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 DESC，也就是如果某条记录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#这个例子中，当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 |+----+----+----+-----+小结