求mysql排序语句

Question

mysqlasselect

Answer 1

mysql> select * from bm;
+--------+----------------+----------------+------+------+------+
| zd1    | zd2            | zd3            | zdj1 | zdj2 | zdj3 |
+--------+----------------+----------------+------+------+------+
| 不限   |                |                | 12.3 |      |      |
| 自定义 | 天津市、吉林省 | 南开区、通化市 |      | 13   | 8.5  |
| 自定义 | 北京市、天津市 |                |      | 9    |      |
| 自定义 | 北京市、天津市 | 崇文区、南开区 |      | 2.7  | 11   |
| 自定义 | 北京市、天津市 | 大港区         |      | 10.6 | 17   |
+--------+----------------+----------------+------+------+------+
5 rows in set (0.00 sec)mysql>mysql> select zd1, zd2 ,zd3 ,zdj1, zdj2, zdj3,GREATEST(zdj1+0, zdj2+0, zdj3+0) as k
    -> from bm
    -> order by 7 desc;
+--------+----------------+----------------+------+------+------+------+
| zd1    | zd2            | zd3            | zdj1 | zdj2 | zdj3 | k    |
+--------+----------------+----------------+------+------+------+------+
| 自定义 | 北京市、天津市 | 大港区         |      | 10.6 | 17   |   17 |
| 自定义 | 天津市、吉林省 | 南开区、通化市 |      | 13   | 8.5  |   13 |
| 不限   |                |                | 12.3 |      |      | 12.3 |
| 自定义 | 北京市、天津市 | 崇文区、南开区 |      | 2.7  | 11   |   11 |
| 自定义 | 北京市、天津市 |                |      | 9    |      |    9 |
+--------+----------------+----------------+------+------+------+------+
5 rows in set (0.00 sec)mysql>

Answer 2

这样不管是否符合条件的都参与排序了，就像这个（大港区   17）并不在条件内也进行了排序
我上面的有IF(LOCATE('崇文区',zd3),zdj3,0)如果不符合就为0