查询各个科目成绩前三的记录

查询各个科目成绩前三的记录


title: 查询各个科目成绩前三的记录
date: 2019-06-19 19:55:41
updated: 2019-06-19 19:55:41
tags:

  • MySQL

|id|student_id|name|course_id|course_name|score|
|–|–|–|–|–|–|
|1|2005001|张三|1|数学|69|
|2|2005002|李四|1|数学|89|
|3|2005005|王五|1|数学|23|
|3|2005001|张三|2|语文|69|
|3|2005002|李四|2|语文|73|
|3|2005005|王五|2|语文|88|
|1|2005006|赵六|2|语文|69|
|2|2005006|赵六|1|数学|89|
|3|2005007|郑七|2|语文|100|

CREATE TABLE `test` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id`  INT UNSIGNED NOT NULL,
  `name`        VARCHAR(20)  NOT NULL,
  `course_id`   INT UNSIGNED NOT NULL,
  `course_name` VARCHAR(20)  NOT NULL,
  `score`       INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB;

INSERT INTO `test` (`student_id`, `name`, `course_id`, `course_name`, `score`)
VALUES (2005001, '张三', 1, '数学', 69),
       (2005002, '李四', 1, '数学', 80),
       (2005005, '王五', 1, '数学', 23),
       (2005001, '张三', 2, '语文', 69),
       (2005002, '李四', 2, '语文', 73),
       (2005005, '王五', 2, '语文', 88),
       (2005006, '赵六', 2, '语文', 69),
       (2005006, '赵六', 1, '数学', 89),
       (2005007, '郑七', 2, '语文', 100);

1. 用一条 SQL 语句查询出每门课都大于 80 分的学生姓名

理解:分数最小的那门课大于80,自然每门课都大于80了。

SELECT `name` FROM `test` GROUP BY `name` HAVING min(`score`) >= 80;

2. 查询各个科目成绩前三的记录

思路:查出该科成绩超过他的人数小于3的学生,再进行排列

SELECT `student_id`, `name`, `course_id`, `course_name`, `score`
FROM `test` `t1`
WHERE (
  SELECT count(1) FROM `test` `t2`
    WHERE `t1`.`course_id` = `t2`.`course_id`
    AND `t1`.`score` < `t2`.`score`
) < 3
ORDER BY `course_id`, `score` DESC;

发表评论

电子邮件地址不会被公开。 必填项已用*标注