SQL 实现：精准查询 20 岁选课学生学号、姓名、平均与总成绩（去重取最高）

```sql CREATE OR REPLACE VIEW test6_05 AS SELECT sid, name, ROUND(AVG(CASE WHEN rn = 1 THEN score END), 0) AS avg_score, SUM(CASE WHEN rn = 1 THEN score END) AS sum_score FROM ( SELECT sid, name, score, ROW_NUMBER() OVER (PARTITION BY sid, cid ORDER BY score DESC) AS rn FROM student_course ) t WHERE rn = 1 GROUP BY sid, name; ``` ######[AI写代码神器 | 116点数解答 | 2024-10-21 00:46:57]