查询每个班级成绩排名前五的学生及成绩

---

– Table structure for student

---

DROP TABLE IF EXISTS student;
CREATE TABLE student (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
score varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
type varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of student

---

INSERT INTO student VALUES (1, ‘张三’, ‘85’, ‘1’);
INSERT INTO student VALUES (2, ‘李四’, ‘74’, ‘1’);
INSERT INTO student VALUES (3, ‘王五’, ‘85’, ‘2’);
INSERT INTO student VALUES (4, ‘赵六’, ‘66’, ‘2’);
INSERT INTO student VALUES (5, ‘孙七’, ‘83’, ‘3’);
INSERT INTO student VALUES (6, ‘刘一’, ‘33’, ‘3’);
INSERT INTO student VALUES (7, ‘陈二’, ‘44’, ‘3’);
INSERT INTO student VALUES (8, ‘周八’, ‘39’, ‘1’);
INSERT INTO student VALUES (9, ‘吴九’, ‘64’, ‘2’);

SET FOREIGN_KEY_CHECKS = 1;

SELECT
*
FROM
student t
WHERE
( SELECT count( 1 ) FROM student WHERE type = t.type AND score > t.score ) < 1

思路:select * from student 加上where就会每条记录都进行匹配 比如
拿到张三 这时候进入子查询 查询type = 1 且成绩比张三高的 数量为0 0<1 成立 所以这条就被保留下来了
拿到李四 这时候进入子查询 查询type = 1 且成绩比李四高的 数量有一个 1<1 不成立 所以这条没有被保留下来
拿到周八 这时候进入子查询 查询type = 1 且成绩比周八高的 数量有两个 2<1 不成立 所以这条没有被保留下来
同理type = 2的
那么在where后被保留下来的就只有张三\王五\孙七

这里思路非常清晰,并且巧妙的利用了主查询与子查询的执行顺序

这里当子查询需要用到主查询的表进行查询时就会将主查询的每一条记录在子查询中遍历

换一个例子

SELECT
*
FROM
student t
WHERE 1<0

和

SELECT
*
FROM
student t
WHERE 1<2

1<2 就会查出所有

**

你明白了吗?

**