欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql中如何实现 row_number分组求topN的功能

程序员文章站 2022-03-11 16:41:32
业务场景:从成绩表中,查询每门课程的top2的学生的所有信息建表语句:CREATE TABLE `subject` ( `Id` int(11) NOT NULL DEFAULT '0', `subject_name` varchar(200) DEFAULT NULL, `sname` varchar(255) DEFAULT NULL, `score` int(11) DEFAULT NULL, PRIMARY KEY (`Id`)) ENGINE=InnoDB DEFAUL...

业务场景:
从成绩表中,查询每门课程的top2的学生的所有信息

建表语句:

CREATE TABLE `subject` (
  `Id` int(11) NOT NULL DEFAULT '0',
  `subject_name` varchar(200) DEFAULT NULL,
  `sname` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

插入记录:

INSERT INTO subject VALUES(1,'语文','张三',85);
INSERT INTO subject VALUES(2,'语文','李四',72);
INSERT INTO subject VALUES(3,'语文','王五',96);
INSERT INTO subject VALUES(4,'语文','赵六',78);
INSERT INTO subject VALUES(5,'语文','刘七',81);
INSERT INTO subject VALUES(6,'数学','张三',82);
INSERT INTO subject VALUES(7,'数学','李四',99);
INSERT INTO subject VALUES(8,'数学','王五',70);
INSERT INTO subject VALUES(9,'数学','赵六',91);
INSERT INTO subject VALUES(10,'数学','刘七',78);
INSERT INTO subject VALUES(11,'英语','张三',62);
INSERT INTO subject VALUES(12,'英语','李四',93);
INSERT INTO subject VALUES(13,'英语','王五',98);
INSERT INTO subject VALUES(14,'英语','赵六',70);
INSERT INTO subject VALUES(15,'英语','刘七',52);

查询前两名sql:
mysql不支持row_number

SELECT a.*
FROM subject a
WHERE (
	SELECT COUNT(1)
	FROM subject b
	WHERE a.`subject_name` = b.`subject_name`
		AND b.`score` > a.score
) <2
ORDER BY subject_name, score DESC;

本文地址:https://blog.csdn.net/weixin_42123844/article/details/107640844

相关标签: 个人分享 mysql