sql分组后取每组前三

对数据进行分组，取每组前三条数据

INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('1', '张三', '50');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('2', '张三', '60');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('3', '张三', '70');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('4', '张三', '66');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('5', '张三', '55');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('6', '张三', '77');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('7', '李四', '32');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('8', '李四', '45');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('9', '李四', '33');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('10', '李四', '28');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('11', '李四', '60');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('12', '李四', '55');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('13', '王五', '99');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('14', '王五', '107');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('15', '王五', '88');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('16', '王五', '65');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('17', '王五', '72');
INSERT INTO `sakila`.`tt` (`id`, `name`, `score`) VALUES ('18', '王五', '126');

数据如下

 

 题目：分组取每人最好成绩的前三条

select * from tt as t1 where (select count(*) from tt where name=t1.name and score>=t1.score)<=3 ORDER BY score desc 

 

 

题目：取每人最好成绩，按最好成绩排名；

select name,max(score) from tt GROUP BY name ORDER BY max(score) desc 

取每人最平均成绩，按照平均成绩倒序排名

select  `name`,avg(score) from tt GROUP BY `name` ORDER BY avg(score) desc