mysql排序后union顺序错乱🤪

记一次sql排序后union all导致的排序错乱

环境介绍

操作系统:macOS

Mysql:8

问题重现

业务中需要对查询结果进行时间倒序排列，然后将几个查询子集合并，sql如下：

select distinct tmp.user_id          as userId,
                tmp.real_name        as realName,
                tmp.mobile           as mobile,
                tmp.register_time,
                tmp.source
from ((select user_id, real_name, mobile, register_time, '直属粉丝' as source from user where superior_id = ?1)
      union all
      (select user_id, real_name, mobile, register_time, '间推粉丝' as source
       from user
       where superior_id in (select user_id from user where superior_id = ?1)) ) as tmp

业务中需要按照以下顺序进行排序：

Source	registerTime
直属粉丝	2020-10-02 00:00:00
直属粉丝	2020-10-01 00:00:00
间推粉丝	2020-11-02 00:00:00
间推粉丝	2020-11-01 00:00:00

我第一个想法就是分别在两个子查询语句中查询后按照时间排讯，但是执行后发现顺序是错乱的。原因是，order by要比union all执行级别低，一旦两个关键字同时出现，会先执行union，在执行order by

解决方案

由上面想要的结果，我们可以知道需要进行source排序和register倒排，那么解决方案就很明白了，如下：

select distinct tmp.user_id          as userId,
                tmp.real_name        as realName,
                tmp.mobile           as mobile,
                tmp.register_time,
                tmp.od,
                tmp.source
from ((select user_id, real_name, mobile, register_time, '直属粉丝' as source, 0 as od from user where superior_id = ?1)
      union all
      (select user_id, real_name, mobile, register_time, '间推粉丝' as source, 1 as od
       from user
       where superior_id in (select user_id from user where superior_id = ?1)) ) as tmp
order by  tmp.od, tmp.register_time desc;

比之前多增加了一个od字段用于source的排序，register_time用于时间排序,可以避免乱序问题。

由于这个问题还花了点时间，特此记录下！