我有以下設定，它運行良好。我很難弄清楚如何在輸出中顯示課程名稱的語法。在我的測驗案例中，所有行都應該具有幾何值。
此外，我如何使用 rank 或 rank_dense 來限制輸出只顯示平均最高的 1 行？
CREATE TABLE students(student_id, first_name, last_name) AS
SELECT 1, 'Faith', 'Aaron' FROM dual UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM dual UNION ALL
SELECT 3, 'Leslee', 'Altman' FROM dual UNION ALL
SELECT 4, 'Patty', 'Kern' FROM dual UNION ALL
SELECT 5, 'Betty', 'Bowers' FROM dual;
CREATE TABLE courses(course_id, course_name) AS
SELECT 1, 'Geometry' FROM dual UNION ALL
SELECT 2, 'Trigonometry' FROM dual UNION ALL
SELECT 3, 'Calculus' FROM DUAL;
CREATE TABLE grades(student_id,
course_id, grade) AS
SELECT 1, 1, 75 FROM dual UNION ALL
SELECT 1, 1, 81 FROM dual UNION ALL
SELECT 1, 1, 76 FROM dual UNION ALL
SELECT 2, 1, 100 FROM dual UNION ALL
SELECT 2, 1, 95 FROM dual UNION ALL
SELECT 2, 1, 96 FROM dual UNION ALL
SELECT 3, 1, 80 FROM dual UNION ALL
SELECT 3, 1, 85 FROM dual UNION ALL
SELECT 3, 1, 86 FROM dual UNION ALL
SELECT 4, 1, 88 FROM dual UNION ALL
SELECT 4, 1, 85 FROM dual UNION ALL
SELECT 4, 1, 91 FROM dual UNION ALL
SELECT 5, 1, 98 FROM dual UNION ALL
SELECT 5, 1, 74 FROM dual UNION ALL
SELECT 5, 1, 81 FROM dual;
/* average grade of each student */
select s.student_id
, s.first_name
, s.last_name
, round(avg(g.grade), 1) as student_avg
from students s
join grades g
on s.student_id = g.student_id
group by s.student_id, s.first_name, s.last_name
ORDER BY avg(g.grade) DESC;
uj5u.com熱心網友回復：
像這樣的東西？
SQL> with temp as
2 (select s.student_id
3 , s.first_name
4 , s.last_name
5 , c.course_name
6 , round(avg(g.grade), 1) as student_avg
7 , rank() over (order by avg(g.grade) desc) rnk
8 from students s join grades g on s.student_id = g.student_id
9 join courses c on c.course_id = g.course_id
10 group by s.student_id, s.first_name, s.last_name, c.course_name
11 )
12 select student_id, first_name, last_name, course_name, student_avg
13 from temp
14 where rnk <= 3
15 order by rnk;
STUDENT_ID FIRST_ LAST_NAM COURSE_NAME STUDENT_AVG
---------- ------ -------- ------------ -----------
2 Lisa Saladino Geometry 97
4 Patty Kern Geometry 88
5 Betty Bowers Geometry 84.3
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/497750.html