mysql 能做到这样子的排序么~?A表
---------------------------------------
id name p
1 AA A
2 BB A
3 CC B
4 DD E
如果 $p = B;
排序 B(所有p字段为B的排在最前) => A =>C =>D ......=>Z
如果$P = Z;
排序 Z => A => B => ..... =>Y
---------------------------------------
id name p
1 AA A
2 BB A
3 CC B
4 DD E
如果 $p = B;
排序 B(所有p字段为B的排在最前) => A =>C =>D ......=>Z
如果$P = Z;
排序 Z => A => B => ..... =>Y
from A表
order by p=$p desc,P
+------+------+------+
| id | name | P |
+------+------+------+
| 1 | AA | A |
| 2 | BB | A |
| 3 | CC | B |
| 4 | DD | E |
+------+------+------+
4 rows in set (0.00 sec)mysql> select *
-> from A
-> order by P='B' desc,P;
+------+------+------+
| id | name | P |
+------+------+------+
| 3 | CC | B |
| 1 | AA | A |
| 2 | BB | A |
| 4 | DD | E |
+------+------+------+
4 rows in set (0.03 sec)mysql>
CASE WHEN P='B' THEN 0 ELSE 1 END;
高效的做法是直接在程序中使用两个单独的SQL语句
select * from A where P='B'
然后再
select * from A where P!='B' order by P如果不愿意在程序中实现,也可以通过UNION来实现,但效率上略差一点儿。mysql> (select * from A where P='B')
-> union all
-> (select * from A where P!='B' order by P);
+------+------+------+
| id | name | P |
+------+------+------+
| 3 | CC | B |
| 1 | AA | A |
| 2 | BB | A |
| 4 | DD | E |
+------+------+------+
4 rows in set (0.00 sec)mysql>