distinct 和 order by 使用时注意事项

• distinct 和 order by 使用时注意点：
  • 案例：查询最近登录的2个不同的用户

    • 表中的数据如下

      

    • sql语句 按照时间倒序：select a.login_time, a.nike_name from test_data a order by a.login_time desc;

      

    • 按照上面的结果我们把nick_name去除重复后取出前两条使用limit 2: select DISTINCT a.nike_name from test_data a order by a.login_time desc limit 2;

      

      发现结果不对，应该输出： wangWu, liSi。
      

    • 分析问题

      sql 语句优先级， where > group by > having > order by > select > limit 所以导致该sql 
      select DISTINCT  a.nike_name from test_data a order by a.login_time desc limit 2; 取出数据不正确性。
      分析sql执行流程
      1： 首先查询全表
      2： order by a.login_time desc, 按照 a.login_time 排序（倒序）
      3： select distinct 过滤重复，但是具有不确定性。比如A,B 这两条数据重复，则取A , 还是B 。 通过测试来判断具有不确定性。
      4:  limit 2 取出两条数据。
      

    • 测试distinct 不确定性

      • 测试1
        • SQLA: select DISTINCT a.nike_name from test_data a order by a.login_time desc; 结果如下：

          liSi
          zhangSan
          wangWu 
          

        • SQLB: select DISTINCT CONCAT_WS( ', ', a.nike_name,a.login_time) from test_data a order by a.login_time desc; 结果如下：

           wangWu, 2021-09-24 13:14:12
           liSi, 2021-09-24 13:08:40
           liSi, 2021-09-24 13:08:36
           zhangSan, 2021-09-24 13:08:27
           zhangSan, 2021-09-24 13:08:24
           wangWu, 2021-09-24 13:07:03
          

        • SQLC: select a.nike_name from test_data a order by a.login_time desc; 结果如下：

          wangWu
          liSi
          liSi
          zhangSan
          zhangSan
          wangWu 
          

        • 测试结果

          通过这三个sql查询的结果来推测 distinct 的去重逻辑好似为： 有一queue, 向 此queue中末尾添加数据，然后从头部开始检查此队列中是否有重复的。
          如果有重复的并且非尾部数据（因为尾部是你在检测重复之前添加的新数据）则移除。 注意啊，这里看似是这样。然后再看下一个案例。
          

      • 测试2
        • SQLA: select DISTINCT a.nike_name from test_data a order by a.login_time

          wangWu
          zhangSan
          liSi
          

        • SQLB: select DISTINCT CONCAT_WS( ', ', a.nike_name,a.login_time) from test_data a order by a.login_time

           wangWu,2021-09-24 13:07:03
           zhangSan,2021-09-24 13:08:24
           zhangSan,2021-09-24 13:08:27
           liSi,2021-09-24 13:08:36
           liSi,2021-09-24 13:08:40
           wangWu,2021-09-24 13:14:12
          

        • SQLC:select a.nike_name from test_data a order by a.login_time

          wangWu
          zhangSan
          zhangSan
          liSi
          liSi
          wangWu
          

        • 测试结果

          通过这三个sql查询的结果来推测 distinct 的去重逻辑好似为： 有一queue, 先向检查此队列中是否有已经存在的即将要添加的新数据，
          如果不存在则添加此新数据，否则不填加， 逻辑和测试1 中的刚好相反。
          

      • 总结：

        此问题存在的mysql版文为5.6.50。 在最新的5.7.29。不会存在此问题。
        因为如果你用到了 distinct 和 order by,那么必须在select distinct 中指定出来你order by的字段。
        本人的解决思路是，如果不涉及排序，建议使用distinct, 否则请使用 group by 和 max/min(排序字段)。例如
        select b.nike_name , b.login_time from (
        select  a.nike_name, max(a.login_time) as login_time from test_data a GROUP BY a.nike_name 
        ) b order by b.login_time desc