Mysql:查询每门课程成绩的前两名
一张成绩表,查询每门课程的前两名,咋一看无从下手,其实可以理解为表自连接 。
建表语句:
CREATE TABLE `Department` (
`Id` int(11) NOT NULL DEFAULT '0',
`depart_name` varchar(200) DEFAULT NULL,
`Name` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
插入记录:
INSERT INTO Department VALUES(1,'语文','张三',85);
INSERT INTO Department VALUES(2,'语文','李四',72);
INSERT INTO Department VALUES(3,'语文','王五',96);
INSERT INTO Department VALUES(4,'语文','赵六',78);
INSERT INTO Department VALUES(5,'语文','刘七',81);
INSERT INTO Department VALUES(6,'数学','张三',82);
INSERT INTO Department VALUES(7,'数学','李四',99);
INSERT INTO Department VALUES(8,'数学','王五',70);
INSERT INTO Department VALUES(9,'数学','赵六',91);
INSERT INTO Department VALUES(10,'数学','刘七',78);
INSERT INTO Department VALUES(11,'英语','张三',62);
INSERT INTO Department VALUES(12,'英语','李四',93);
INSERT INTO Department VALUES(13,'英语','王五',98);
INSERT INTO Department VALUES(14,'英语','赵六',70);
INSERT INTO Department VALUES(15,'英语','刘七',52);
查询前两名sql:
SELECT a.*
FROM Department a
WHERE 2 > (
SELECT COUNT(1)
FROM Department b
WHERE a.`depart_name` = b.`depart_name`
AND b.`score` > a.score
)
ORDER BY depart_name, score DESC;
版权声明:本文为lhxinbobo原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。