sql查询每科成绩的最高分_MYSQL查询每科前三名的成绩

1.创建总表score；

mysql> use test;

Database changed

mysql> CREATE TABLE score (

-> id int(10) NOT NULL AUTO_INCREMENT,

-> subject_id int(10) DEFAULT NULL,

-> student_id int(10) DEFAULT NULL,

-> score float DEFAULT NULL,

-> PRIMARY KEY (id)

-> ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected, 4 warnings (0.61 sec)

2.输入表中内容；

mysql> INSERT INTO SCORE VALUES(1,1,1,96),(2,1,2,86),(3,1,3,99),(4,1,4,78),(5,1,5,89);

Query OK, 5 rows affected (0.43 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO SCORE VALUES(6,2,1,97),(7,2,2,88),(8,2,3,98),(9,2,4,79),(10,2,5,87);

Query OK, 5 rows affected (0.04 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO SCORE VALUES(11,3,1,99),(12,3,2,87),(13,3,3,97),(14,3,4,77),(15,3,5,88);

Query OK, 5 rows affected (0.12 sec)

Records: 5 Duplicates: 0 Warnings: 0

3.输入具体的表内容；

mysql> CREATE TABLE student (

-> id int(10) NOT NULL AUTO_INCREMENT,

-> name varchar(10) DEFAULT NULL,

-> PRIMARY KEY (id)

-> ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

Query OK, 0 rows affected, 2 warnings (0.30 sec)

INSERT INTO STUDENT VALUES(1,'张斌'),(2,'李四'),(3,'王五'),(4,'周六'),(5,'张三');

Query OK, 5 rows affected (0.72 sec)

Records: 5 Duplicates: 0 Warnings: 0

INSERT INTO SUBJECT VALUES(1,'java'),(2,'c++'),(3,'python');

Query OK, 3 rows affected (0.53 sec)

Records: 3 Duplicates: 0 Warnings: 0

4.查询

select a.id,a.subject_id,a.student_id,a.score from score as a left join score as b on a.subject_id=b.subject_id and a.score>=b.score

group by a.subject_id,a.student_id,a.score

having count(a.subject_id)>=3

order by a.subject_id,a.score desc;

+----+------------+------------+-------+

| id | subject_id | student_id | score |

+----+------------+------------+-------+

| 3 | 1 | 3 | 99 |

| 1 | 1 | 1 | 96 |

| 5 | 1 | 5 | 89 |

| 8 | 2 | 3 | 98 |

| 6 | 2 | 1 | 97 |

| 7 | 2 | 2 | 88 |

| 11 | 3 | 1 | 99 |

| 13 | 3 | 3 | 97 |

| 15 | 3 | 5 | 88 |

+----+------------+------------+-------+

9 rows in set (0.00 sec) #这里把所有的列都列出来了便于对比 这里把表score的每一条同subject_id的数据都连接起来

#按subject_id,student_id,score来进行分组； #添加count(a.subject_id)来进行对比易于理解

分组后再进行条件查询；

接下来就是排序：