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)来进行对比易于理解
分组后再进行条件查询；
接下来就是排序：
