在mysql中不能一起使用union和order by
比如:
select name,age,gender from user order by age where gender = '男'
union
select name,age,gender from user order by age where gender = '女'
从表面上看是没有问题,但是实际上不能达到效果
别的博文说使用子查询,把排序后的结果作为临时表再查询,我也使用过,也不行
最终在starkoverflow中找到了答案:建立一个临时字段来解决
select *
from (
select 1 as rank, username, age, gender from user where gender='男'
union all
select 2 as rank, username, age, gender from user where gender='女'
) a
order by rank, age
还有一种解决办法
在order by使用的语句中加上limit
select name,age,gender from user order by age where gender = '男' limit 100
union
select name,age,gender from user order by age where gender = '女' limit 100