My SQL group by取同组第一条

￡神魔★判官ぃ 2023-07-07 03:56 84阅读 0赞

5.6测试满足。

8.0测试不OK。

网上说的一套一套的，基本都是低版本MySQL5.6。

前情提示：

Mac OS10.14；

MySQL8.0.18；localhost；这里插一句，MySQL高版本5.7以后，sql\_mode有变化，其中之一体现为，group by写法。可查看文章了解：

远程centOS6.8final，MySQL5.6；

表结构，数据都是一样；

测试SQL语句：

SELECT SwipeID,MemberID,AddTime FROM
    (
    SELECT * FROM front_membercardswipe fmcs
    ORDER BY fmcs.AddTime ASC
    ) a
    GROUP BY a.MemberID

改变语句中子查询中的ASC或者DESC，可以清楚看到选择的是默认同组第一条，这样的话很容易先排序后分组，比如选择不同用户的最新、最早的某一记录；

5.6结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 1][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 2][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 3][]

8.0本地测试截图：与上图一一对应

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 4][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 5][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 6][]![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 7][]

看出问题了吗？8.0的最后一张，按想的排序再分组，应该是SwipeID=1、3、4.

解决：

多搜索，多尝试，我感觉这些语法介绍说明都在5.7或者8.0官方文档里介绍了的，建议大家以后以官方更新文档为主。确实更新挺大的，让我不禁想到centOS6与centOS7，MacOS10.15与MacOS10.15Catalina。

8.0正确SQL示例：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 8][] 大致意思，在子查询中为了优化，order by需要和limit配合使用，否则会忽略order by。

其实解决办法还有其他的，比如结合具体实际业务，换种SQL写法，而不是使用子查询limit。

另外附送大家一条SQL：

SELECT * FROM front_membercardswipe fmcs ORDER BY fmcs.SwipeID,fmcs.AddTime 
    SELECT * FROM front_membercardswipe fmcs ORDER BY fmcs.SwipeID DESC,fmcs.AddTime DESC 
    SELECT * FROM front_membercardswipe fmcs ORDER BY fmcs.SwipeID DESC,fmcs.AddTime ASC

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200220211322736.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200220211347205.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200220211411904.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200220211433728.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200220211857424.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2020022021192118.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200220212005675.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200220212110373.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/2020022021514752.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70