MySQL之分组选择数据

/*用到的表*/
DROP TABLE IF EXISTS `friends`;/*悟空朋友表*/
CREATE TABLE `friends` (
  `id` int(3) NOT NULL,/*sql server中不能指定int长度*/
  `user_name` varchar(30) default NULL,
  `type` varchar(30) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `friends` VALUES ('1', '孙悟空', 'friends');
INSERT INTO `friends` VALUES ('2', '牛魔王', 'friends');
INSERT INTO `friends` VALUES ('3', '蛟魔王', 'friends');
INSERT INTO `friends` VALUES ('4', '鹏魔王', 'friends');
INSERT INTO `friends` VALUES ('5', '狮驼王', 'friends');

DROP TABLE IF EXISTS `qujing`;/*取经表*/
CREATE TABLE `qujing` (
  `id` int(3) NOT NULL,
  `user_name` varchar(30) default NULL,
  `type` varchar(30) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `qujing` VALUES ('1', '唐僧', 'qujing');
INSERT INTO `qujing` VALUES ('2', '猪八戒', 'qujing');
INSERT INTO `qujing` VALUES ('3', '孙悟空', 'qujing');
INSERT INTO `qujing` VALUES ('4', '沙僧', 'qujing');

分组查询：选出每组前两名

select d.user_name ,c.timestr,kills
from(
select user_id,timestr,kills,(select  count(*) from
user_kills  b  where   b.user_id=a.user_id  and  a.kills<=b.kills)as  cnt
   from  user_kills  a
  group by user_id,timestr,kills
) c  join user1 d on c.user_id=d.id
where cnt<=2

每个人杀怪最多的前两个日期

select  a.user_name,b.timestr,b.kills
from  user1  a  join  user_kills b
on  a.id=b.user_id
where user_name=’孙悟空’
order  by  b.kills  desc 
limit  2

查询出孙悟空杀怪最多的前两天是哪两天

注意：在MySQL中不支持ROW_NUMBER()函数

优化方式1：采用ROE_NUMBER函数，进行分区排序

优化方式2：

select  d.user_name,c.timestr,kills  from(
select  user_id,timestr,kills,(select  count(*) from user_kills b 
where b.user_name =a.user_id  and a.kills <=b.kills) as  cnt  

from user_kills a group by user_id,timestr,kills ) c join user1 d 
on c.user_id=d.id  where  cnt<=2;

参考视频：http://www.imooc.com/learn/398