sql联合命令 [英] sql union order

问题描述

我有一个桌子的学生，上面有名字和身高.我想要一个查询，以字母顺序将大于150cm的学生和小于150cm的学生按名字的降序排序.

I have a table students with names and heights. I want a query that order students higher that 150cm alphabetically and students smaller than 150cm in descending order of their names.

类似这样的东西:

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

它不起作用，因为联合使子查询中的行顺序混乱.我知道这很正常，并集输出一个集，并且按集的顺序排列并不重要.是否有类似append的内容?

it's not working because union mess up the order of the rows in subqueries. I know it's normal, union output a set and in a set the order it's not important. Is there something like append?

推荐答案

   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 |
+------+--------+

这篇关于sql联合命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！