mysql UNION 或UNION ALL 与OEDER BY 冲突的解决。

使用mysql用union或union all并子集中用order by排序顺序会混乱

(select id from a order by start_time asc)

     union all

    (select id from b order by start_time desc) limit 0, 20

这样的写法会导致排序混乱

解决方法如下

1  使用limit

(select id from a order by start_time asc limit 99999999)

     union all

    (select id from b order by start_time desc limit 99999999) limit 0, 20

2  在子查询句外面包一层

select * from

(select id from a order by start_time asc) t1

union all

select * from

(select id from b order by start_time desc) t2 limit 0, 20