union和union all后导致排序失效的问题
1. 单独运行子查询,排序没有问题:
子查询1:
SELECT warning_level,occur_date FROM `platform_event_result`where status=1 and warning_level in (2,3) order by occur_date
子查询2:
SELECT warning_level, occur_date FROM `platform_event_result`where status=1 and warning_level in (4,5) order by occur_date
2.使用union all或union后:
(SELECT warning_level,occur_date FROM `platform_event_result`where status=1 and warning_level in (2,3) order by occur_date )
UNION ALL
(SELECT warning_level, occur_date FROM `platform_event_result`where status=1 and warning_level in (4,5) order by occur_date )
3.加上limit后排序正常:
(SELECT warning_level,occur_date FROM `platform_event_result`where status=1 and warning_level in (2,3) order by occur_date LIMIT 999)
union ALL
(SELECT warning_level, occur_date FROM `platform_event_result`where status=1 and warning_level in (4,5) order by occur_date LIMIT 999)
注:limit后的值999可以随便给,但是不能小于子查询的查询结果的总条数,否则查询的结果中会少记录,如果不确定子查询结果会有多少条,就尽量取大一些;