SQL查询一个表中类别字段中Max()最大值对应的记录

2011-10-09 07:52 默认分类狂奔的蜗牛

问题是：

数据库有一个表 code，里面有个点击量字段click_num和一个类别字段kind以及其它信息字段，

现在要搜出每个类别中点击量最大的那条记录，如果是10个类别，那么结果应该是10条记录，

如果最大点击量有两个相同的只要一条。

经过N次搜索，N次检测网上的解决SQL语句，终于找到个优雅的而且结果正确的SQL，这个是一个博客作者在Mysql的官方文档里面发现的。

禁不住收藏了，以备后用。

select id,kind,click_num from code as a 
	where  click_num=(select max(b.click_num)
					  from code as b
					  where a.kind = b.kind
					  );
特别注意：

这个语句在SQLite上测试正常，速度很快。但是在我机器上的MySql5.0上执行后就死机了，其他版本的Mysql不知道什么情况。

上面的语句还不能满足我的第二个要求就是：如果某类别中最大点击量有两个相同的记录，只要一条。

下面是我附加的解决办法：

筛选，保证每个类别只有一条,（kind,id改为所有列,结果就是对应的记录了）

select  * 
	from (select id,kind,click_num from code as a 
							   where  click_num=(select max(b.click_num)
												  from code as b
												  where a.kind = b.kind
												 )
		 ) as a
	group by kind
后续：

结合以往的Mysql使用经验发现Mysql的一个最大问题就是在一个查询的where子句中如果包含对前面select的表的查询那么CPU占用就会飙升Mysql服务停止。可以说是自连接查询问题，不知道这是不是个bug，5.0,5.1版本都有这个问题，回头看看上面的SQL语句正好符合了这个问题，解决办法就是把子查询的表弄成一个临时表或者视图总之不能和前面的select的表是同一个表，那么问题就解决了，于是乎，对上面的SQL稍作修改就可以在Mysql上跑了。

修改过程：

1.建立一个视图max_click,存储的是每个类别最大的点击量

create view max_click as select max(click_num) as click_num,kind 
							 from code 
							 group by kind;

2.筛选，保证每个类别只有一条记录

select  * 
	from (select id,kind,click_num
		  from  code
		  where click_num = ( select b.click_num
								  from max_click as b
								  where code.kind = b.kind
							   )
		  ) as a
	group by kind;
运行正常，速度0.78秒。（表中一共23000条数据） Okay! 大功告成！

最前面第一个SQL在MSSQL上运行不知道行不行，我想在SQLite能正常在MSSQL上应该没问题，看来Mysql需要改进的地方很多啊，同样是开源的SQLite就可以做到的，而且SQLite那么轻量级就几百KB，Mysql更应该能做到。

作者：狂奔的蜗牛 出处： http://www.host900.com/

本文版权归作者所有，欢迎转载。未经作者同意下，必须在文章页面明显标出原文链接及作者，否则保留追究法律责任的权利。

如果您认为这篇文章还不错或者有所收获，可以分享给您的小伙伴，因为你的支持是我继续写作，分享的最大动力！

上一篇随笔

下一篇织梦CMS：标题截断后，超级链接title显示完全的方法