2020-05-09

mysql 5.7 group by 显示最新（想要的记录）

test 表格中的数据是这样的：
    id     seq  name    
------  ------  --------
     1       1  name1   
     2       2  name2   
     3       1  name2   
     4       2  name1  
查询语句是这样的：
SELECT * FROM (SELECT * FROM test ORDER BY seq DESC ) tmp GROUP BY NAME
预期的结果时:
    id     seq  name    
------  ------  --------
     2       2  name2   
     4       2  name1  
但是实际结果却是这样的：
    id     seq  name    
------  ------  --------
     1       1  name1   
     2       2  name2  

有效写法

SELECT * FROM (SELECT * FROM test ORDER BY seq DESC limit 10000000000) tmp GROUP BY NAME

通过 explain 查看执行计划，可以看到没有 limit 的时候，少了一个 DERIVED 操作
估计是内部优化了，认为 ORDER BY 在这种语法中可忽略
有 LIMIT 限制涉及排序后的结果，不会忽略 ORDER BY，可以达到预期

需要增加limit
原理分析：
我们这里使用了临时表排序，继而对其结果进行分组，结果显示失败，加了limit后结果正确，原因是因为临时表中使用order by且使其生效，必须满足三个条件：
1：外部查询禁止分组或者聚合
2：外部查询未指定having，HAVING， order by
3：外部查询将派生表或者视图作为from句中唯一指定源
不满足这三个条件，order by会被忽略。

也可以用group_concat 配合 SUBSTRING_INDEX 实现