MySQL 之union all 与 order by 同时出现问题

最近项目中有一个小需求，用到了order by和 union，遇到了一点小问题，相信大家以后也会有类似的需求，与大家分享。

先说一下我们的需求：就是在分页查询活动信息的时候，要求的显示格式是先显示未结束的活动按照最近要结束的放在前面，然后显示已经结束的活动，按照最新结束的在上面，这样的需求自然而然的想到了union all，union all和union的区别大家应该都知道就是union去重，union all不去重，开始写的语句是这样的（上代码有点乱，大家凑活着看吧~~~）

 (select t.*,d.* from

 (select a.id activeId,a.name activeName,a.base_gold baseGold,

 a.start_time startTime,a.end_time endTime,a.valid, betLog.num,

 betLog.u_center_id uid from t_user_bet_log betLog

 left join t_active a on betLog.active_id = a.id group by activeId,uid )t

 left join t_draw d on t.activeId = d.active_id

 WHERE t.uid= 3000 AND  t.valid=1 ANDt.startTime<=now() AND t.endTime >= now() AND ISNULL(d.id) order by t.endTime asc)

 union all

 (select t.*,d.* from

 (select a.id activeId,a.name activeName,a.base_gold baseGold,

 a.start_time startTime,a.end_time endTime,a.valid,

 betLog.num,betLog.u_center_id uid from t_user_bet_log betLog

 left join t_active a on betLog.active_id = a.id group by activeId,uid)t

 left join t_draw d on t.activeId = d.active_id

 WHERE t.uid= 3000 AND t.valid=1 AND ((t.endTime<now()) OR (d.active_team_id=-1))order by t.endTime desc)

 

加粗的是主要逻辑，单独执行子查询顺序都是正确的，但是当两个子查询union all的时候就会出现顺序混乱的情况，为了一下兆满说union all和union都是会影响子查询的输出结果，所以要将语句改造,下面是我改造的语句，主要思想就是先给两个子查询排序，让union all 不会影响子查询的顺序，再处理一下时间变成自己需要的输出形式：

 

select * from(

 (select t.*,d.*,10 'idx',endTime - now() time1 from

 (select a.id activeId,a.name activeName,a.base_gold baseGold,

 a.start_time startTime,a.end_time endTime,a.valid, betLog.num,

 betLog.u_center_id uid from t_user_bet_log betLog

 left join t_active a on betLog.active_id = a.id group by activeId,uid )t

 left join t_draw d on t.activeId = d.active_id

 WHERE t.uid= 3000 AND  t.valid=1 ANDt.startTime<=now() AND t.endTime >= now() AND ISNULL (d.id))

 union all

 (select t.*,d.*,20 'idx',now()-endTime time1 from

 (select a.id activeId,a.name activeName,a.base_gold baseGold,

 a.start_time startTime,a.end_time endTime,a.valid,

 betLog.num,betLog.u_center_id uid from t_user_bet_log betLog

 left join t_active a on betLog.active_id = a.id group by activeId,uid)t

 left join t_draw d on t.activeId = d.active_id

 WHERE t.uid= 3000 AND t.valid=1 AND ((t.endTime<now()) OR (d.active_team_id=-1)))

 order by idx,time1 asc)ttt

上代码就是有点乱。。。一个asc和一个desc，小处理了一下都变成了asc的。