最近做数据统计,类如数据库里明明有11月30号的数据,我也明明按时间降序排序了,可是最终获取的数据时间,却不是11月30号这个最大时间,而是其它时间。
最终找出的问题原因就是:mysql中order by是在group by之后执行的,而group by则是在同一组数据里挑选出id最小的数据记录作为汇总记录
可能有点模糊,下面拿我的博客举例子。
在实践之前,我先贴出我的服务器mysql版本:mysql Ver 15.1 Distrib 5.5.56-MariaDB
假如我要查询我的博客每个分类下最新的文章,我写了如下Sql语句:select count(*) as count,news_id,title, cate_id, from_unixtime(create_time) from news n group by n.cate_id order by n.create_time desc;
查询的结果如下图:
![5d0c32b273f7a8026bef4dab6ad0eede.png](https://img8.php1.cn/3cdc5/18c3f/978/8030ae361d7411e5.png)
图中高光的是我博客的php分类,但php分类下我最近一直在更新文章,所以php分类下最新的文章发布时间绝对不会是2017年。
正如上面原因提到的,group by会先于order by从我的博客每个分类下的文章组里挑出当组id最小的数据来展示。
所以网上常有group by和order by连用后数据排序不对的问题。
我把上面sql语句,改写成如下,即在子查询里先用order by将数据按时间降序排号,然后再group by:SELECT count(*) as count, tmp.* FROM (SELECT news_id,title, cate_id, from_unixtime(create_time) FROM news ORDER BY create_time DESC) tmp GROUP BY tmp.cate_id
我以为会成功,事实并没有,此次执行结果如下图所示:
![99195c81bd0fea39737b495ee54da971.png](https://img8.php1.cn/3cdc5/18c3f/978/0cd5cd8de896ad8e.png)
由图中高光时间可知,并未成功。
经过了解, 上面mysql子查询里order by之后必须要跟上limit,否则mysql优化会将上面mysql语句里的order by给优化掉。
改成如下,执行结果就ok了。看图中数据的时间部分就可辨别。SELECT count(*) as count, tmp.* FROM (SELECT news_id,title, cate_id, from_unixtime(create_time) FROM news ORDER BY create_time DESC LIMIT 1000) tmp GROUP BY tmp.cate_id
执行结果:
![d57454cc781eafc6c6831d2dc4bd2361.png](https://img8.php1.cn/3cdc5/18c3f/978/08837dd9cf651578.png)
本文是翟码农个人博客蓝翟红尘里php分类下的文章,转载请注明出处:http://www.zhai14.com/blog/2ade3f69c8410f534658e5a1d98b0ea3.html
好了,上面是Mysql语句解决order by和group by混用排序失效的方法。
那么在Laravel框架中,该怎么实现上述mysql语句的效果呢。
废话不多说了,直接上代码吧:$query = News::where('created_at', '>', $startTime)
->where('created_at', '
->where('status', 1)
->whereIn('cate_id', [1,2,3,4])
->select(DB::raw(" news_id,title, cate_id, from_unixtime(create_time) "));
$binding = $query->getBindings(); //获取where条件数据
$sqlStr = $query->toSql();
//上面sqlStr打印出来,其中条件参数都是问号?,现在我们要将其替换成where条件的具体值
$afterSql = str_replace('?', "'?'", $sqlStr);
$realSql = vsprintf(str_replace('?', '%s', $afterSql), $binding );
$sortResult = DB::table(DB::raw("({$realSql}) as tmp"))
->groupBy('tmp.cate_id')
->get();
上面代码里DB::table后面调用get方法得到的数据结构形式如下:[ Object(StdClass), Object(StdClass)]
foreach遍历上述结果$sortResult,每个Item都是对象,导致不能数组方式获取数据。
如果要用数组方式获取数据,就需要先将数组处理下。
数组不大时,即不考虑什么变量空间,采取如下解决方案即可:$newSortResult = array_map(function($item){
return $item->toArray();
}, $sortResult);
2019年12月04日 12:41文章创建
2019年12月04日 23:21文章发布