最近做数据统计,类如数据库里明明有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;
查询的结果如下图:
图中高光的是我博客的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
我以为会成功,事实并没有,此次执行结果如下图所示:
由图中高光时间可知,并未成功。
经过了解, 上面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
执行结果:
本文是翟码农个人博客蓝翟红尘里php分类下的文章,转载请注明出处:http://www.zhai14.com/blog/2ade3f69c8410f534658e5a1d98b0ea3.html
好了,上面是Mysql语句解决order by和group by混用排序失效的方法。
那么在Laravel框架中,该怎么实现上述mysql语句的效果呢。
废话不多说了,直接上代码吧:
$query = News::where('created_at', '>', $startTime) ->where('created_at', '<', $endTime) ->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);