mysql group by 取orderby第一条数据，laravel原生sql与在数据库中执行结果不一致问题

青旅半醒 2023-05-29 13:02 16阅读 0赞

需求：查询分组，取分组中排序的第一条数据  
分析：由于mysql中 groupby和orderby 是先进行分组再进行排序，结果不是我们需要的。我们需要的结果是先排序在分组自动取每个排序的第一条数据，写法如下：

select * from (select * from `tb_autocommit`  where `hwsn` in ('13G0113JA180148') 
    ORDER BY testitems desc) AS a GROUP BY a.hwsn

![在这里插入图片描述][20191106105249501.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70]  
这个结果是我们需要的

在laravel中执行这条原生语句

$test = DB::select("select * from (select * from `tb_autocommit`  where
                 `hwsn` in ('13G0113JA180148')ORDER BY testitems desc) AS a GROUP BY a.hwsn
    ");
                return $test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 2]  
这里出现了一个原生mysql语句在laravel中运行和数据库中运行结果不一致，laravel似乎排序没有起作用；这导致这个查询功能无法实现，有什么办法能够去解决这个问题呢？

我们使用groupby中min和max进行尝试，查看结果：

SELECT * ,MAX(testitems) as test FROM tb_autocommit where hwsn in('13G0113JA180148') GROUP BY hwsn

![在这里插入图片描述][20191106110148258.png]  
查看test中的结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 3]  
是我们需要的，再看看laravel中的结果；

$test = DB::select("SELECT * ,MAX(testitems) as test FROM tb_autocommit where hwsn in('13G0113JA180148') 
     GROUP BY hwsn");
                return $test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 5]  
现在laravel中的结果和数据库中执行的结果一致

[20191106105249501.png]: https://img-blog.csdnimg.cn/20191106105249501.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191106105321619.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191106105540260.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191106105616895.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[20191106110148258.png]: https://img-blog.csdnimg.cn/20191106110148258.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191106110221409.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191106110337954.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191106110438957.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzY3NDExMw==,size_16,color_FFFFFF,t_70