oracle查一个学科里成绩最高的学生的信息

mysql> select *  from  student_score;
+-----------+------------+-------+
| course_id | student_id | score |
+-----------+------------+-------+
|         1 |          1 | 100   |
|         2 |          2 | 200   |
|         3 |          3 | 9900  |
|         4 |          4 | 200   |
|         2 |          5 | 2000  |
|         3 |          6 | 9999  |
|         2 |          9 | 9013  |
+-----------+------------+-------+
7 rows in set

mysql> select *  from  student;
+----+------------+
| id | name       |
+----+------------+
|  1 | 学大数据的 |
|  2 | 学志       |
|  3 | 徐盛       |
|  4 | 沈娇娇     |
|  5 | 静博       |
|  6 | 黄磊       |
|  9 | 泽阳       |
+----+------------+
7 rows in set

mysql> select *  from  course;
+----+--------+
| id | name   |
+----+--------+
|  1 | 大数据 |
|  2 | java   |
|  3 | 前端   |
|  4 | ui     |
+----+--------+
4 rows in set



--查一个学科里成绩最高的学生的信息
SELECT *
FROM (
	SELECT s.name
		,sc.score
		,c.name name2
	FROM student s
		,student_score sc
		,course c
	WHERE s.id = sc.student_id
		AND sc.course_id = c.id
	) s1
	,(
		SELECT s1.name2
			,max(s1.score) score
		FROM (
			SELECT s.name
				,sc.score
				,c.name name2
			FROM student s
				,student_score sc
				,course c
			WHERE s.id = sc.student_id
				AND sc.course_id = c.id
			) s1
		GROUP BY s1.name2
		) s2
WHERE s1.name2 = s2.name2
	AND s1.score = s2.score;
+------------+-------+--------+--------+-------+
| name       | score | name2  | name2  | score |
+------------+-------+--------+--------+-------+
| 学大数据的 | 100   | 大数据 | 大数据 | 100   |
| 沈娇娇     | 200   | ui     | ui     | 200   |
| 黄磊       | 9999  | 前端   | 前端   | 9999  |
| 泽阳       | 9013  | java   | java   | 9013  |
+------------+-------+--------+--------+-------+