最近项目中有一个小需求,用到了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的。