sql联合顺序

Question

我有一张桌子，上面有学生的姓名和身高。我想要一个查询，按字母顺序对身高高于 150 厘米的学生进行排序，对身高低于 150 厘米的学生按姓名降序排列。

像这样的事情：

(select * from students where height >= 150 order by name)  
union 
(select * from students where height < 150 order by name desc)

它不起作用，因为联合弄乱了子查询中行的顺序。我知道这是正常的，联合输出一个集合，并且在一个集合中的顺序并不重要。有类似追加的东西吗？

Answer 1

   SELECT *
   FROM students
   ORDER BY IF(height >= 150, 1,0 ) DESC, 
            IF(height >= 150, name, '') ASC, 
            name DESC

样本输出

+------+--------+
| name | height |
+------+--------+
| a    |    189 |
| m    |    666 |
| thy  |    166 |
| yyy  |   1277 |
| zz   |    101 |
| swq  |    122 |
| n    |    111 |
| g    |    145 |
+------+--------+

Answer 2

select *, case when height >= 150 then 1 else 0 end as tallstudent 
from students order by tallstudent, name