Mar 26

mysql “group by ”与"order by"的研究--分类中最新的内容 不指定

bkkkd , 09:53 , 开发应用 , 评论(3) , 引用(0) , 阅读(55644) , Via 本站原创 | |
引用
之前掉失的日志,还好有人引用了。不然我也找不到了。

这两天让一个数据查询难了。主要是对group by 理解的不够深入。才出现这样的情况
这种需求,我想很多人都遇到过。下面是我模拟我的内容表.
点击在新窗口中浏览此图片
我现在需要取出每个分类中最新的内容
select * from test group by category_id order by `date`

结果如下
点击在新窗口中浏览此图片
明显。这不是我想要的数据,原因是msyql已经的执行顺序是
写的顺序:
select ... from... where.... group by... having... order by..

执行顺序:
from... where...group by... having.... select ... order by...

所以在order by拿到的结果里已经是分组的完的最后结果。
由from到where的结果如下的内容。
select * from test where 1

点击在新窗口中浏览此图片
到group by时就得到了根据category_id分出来的多个小组

select * from test where category_id=1

点击在新窗口中浏览此图片
select * from test where category_id=2

点击在新窗口中浏览此图片
到了select的时候,只从上面的每个组里取第一条信息结果会如下
select * from test where 1 group by category_id

点击在新窗口中浏览此图片
即使order by也只是从上面的结果里进行排序。并不是每个分类的最新信息。
回到我的目的上 --分类中最新的信息
根据上面的分析,group by到select时只取到分组里的第一条信息。有两个解决方法
1,where+group by(对小组进行排序)
2,从form返回的数据下手脚(即用子查询)

由where+group by的解决方法
对group by里的小组进行排序的函数我只查到group_concat()可以进行排序,但group_concat的作用是将小组里的字段里的值进行串联起来。
select group_concat(id order by `date` desc) from `test` group by category_id

点击在新窗口中浏览此图片
select * from `test` where id in(select SUBSTRING_INDEX(group_concat(id order by `date` desc),',',1) from `test` group by category_id ) order by `date` desc

点击在新窗口中浏览此图片
子查询解决方案
select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc

点击在新窗口中浏览此图片


2014-1-12网友:skywalker_lan 提出子查询解决方案最后面增加order by是没有作用。基于研究精神,还是测试一次。
附上测试用的数据表sql
CREATE TABLE `test` (  `id` INT(10) NOT NULL AUTO_INCREMENT,  `name` CHAR(50) NOT NULL,  `category_id` INT(10) NOT NULL,  `date` DATETIME NOT NULL,  PRIMARY KEY (`id`))COLLATE='utf8_general_ci'ENGINE=MyISAM;REPLACE INTO `test` (`id`, `name`, `category_id`, `date`) VALUES (1, 'aaa', 1, '2013-12-12 12:12:12');REPLACE INTO `test` (`id`, `name`, `category_id`, `date`) VALUES (2, 'bbb', 2, '2013-12-13 12:12:12');REPLACE INTO `test` (`id`, `name`, `category_id`, `date`) VALUES (3, 'ccc', 1, '2013-12-14 12:12:12');REPLACE INTO `test` (`id`, `name`, `category_id`, `date`) VALUES (4, 'ddd', 1, '2013-12-15 12:12:12');REPLACE INTO `test` (`id`, `name`, `category_id`, `date`) VALUES (5, 'eee', 2, '2013-12-16 12:12:12');

点击在新窗口中浏览此图片
查询不带order by的数据:
select * from (select * from `test` order by `date` desc) `temp`  group by category_id;

点击在新窗口中浏览此图片
查询不带order by的数据:
select * from (select * from `test` order by `date` desc) `temp`  group by category_id order by `date` desc;

点击在新窗口中浏览此图片
具体原因没有看mysql的源码,所以不了解。不过结果很明显,子查询返回的结果是有排序,但group by后,排序发生改变(原因不详),所以才出现必须在后面再增加order by.
Tags: , ,
skywalker_lan
December 5, 2013 11:16
再来看一看,不过使用子查询的时候,外面的order by 应该不用了,而且也是不起作用的。
select * from (select * from `test` order by `date` desc) `temp`  group by category_id;

select * 中 没有聚合函数,select 也没有分组中的过滤条件 category_id, 所以这样严格来说应该也有问题。只不过mysql 在这钟情况下,默认把第一条数据返回到查询结果; 也就是分组之后各自得到一条数据;  此时外面再加的 order by 应该没什么意义了。
bkkkd 回复于 January 14, 2014 06:51
不知道具体原因,但确实结果不一样,你可以看一下文章最后增加的分析。
evens205 Email Homepage
September 21, 2012 17:01
嗯,不错,分析的很好啊,以后多过来看看
108918tcy Email Homepage
May 13, 2012 18:54
途经看一下!!!!!!!!!!!!!
分页: 1/1 第一页 1 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]