Mysql 分学科拿取前三名的数据

留存—

参考地址
数据准备：

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` varchar(32) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  `subject` varchar(30) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `test` VALUES ('1', '张三', '语文', '98');
INSERT INTO `test` VALUES ('2', '张三', '数学', '80');
INSERT INTO `test` VALUES ('3', '张三', '英语', '90');
INSERT INTO `test` VALUES ('4', '李四', '语文', '88');
INSERT INTO `test` VALUES ('5', '李四', '数学', '86');
INSERT INTO `test` VALUES ('6', '李四', '英语', '88');
INSERT INTO `test` VALUES ('7', '李明', '语文', '60');
INSERT INTO `test` VALUES ('8', '李明', '数学', '86');
INSERT INTO `test` VALUES ('9', '李明', '英语', '88');
INSERT INTO `test` VALUES ('10', '林风', '语文', '74');
INSERT INTO `test` VALUES ('11', '林风', '数学', '99');
INSERT INTO `test` VALUES ('12', '林风', '英语', '59');
INSERT INTO `test` VALUES ('13', '严明', '英语', '96')

方法一

先给每科成绩排名，也就是表中两个条件，一是科目一样，在此之下，算出比自己score分数高的人数和sum,则sum+1就是自己的名次；
首先sum 的算法：主体为一张表，去镶嵌一个排名，
select t1.* ,排名，from test t1;

排名：逻辑 --并不能运行

SELECT count(*) + 1 FROM test  WHERE test.subject = test.subject AND test.score < test.score

之后就考虑区分表
这边的t1.score<t2.score是因为主表(被比较表)是t1，t2中有几条数据能比t1大，加一则是排名

select  t1.* ,(SELECT count(*) + 1 FROM test  t2  WHERE t1.subject = t2.subject AND t1.score 
< t2.score) as top from test t1;

加上排序 order by subject score

select  t1.* ,(SELECT count(*) + 1 FROM test  t2  WHERE t1.subject = t2.subject AND t1.score 
< t2.score) as top from test t1 order by subject,score

结果：

接下来是取前三条数据：
加一个having，这里这种方法就是按人头算名次，并列第2则没有第三名

select t1.*,(select count(*)+1 from test t2 where t2.subject = t1.subject and t2.score > t1.score)as top 
from test t1 HAVING top <4 order BY
subject,score desc

方法一.2

一样，没展示出排名，揉到子查询里取筛数据

SELECT
		t1.*
FROM
test t1
where (SELECT count(*) + 1 FROM test t2 WHERE t2.subject = t1.subject AND t2.score > t1.score ) <=3
ORDER BY t1.subject

方法二

group by 有会的，请留言