mysql查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

封面图 • 2025-10-13 • MySQL

要查询所有课程中成绩 **第2名到第3名** 的学生信息及该课程的成绩，可以使用 MySQL 中的窗口函数（`ROW_NUMBER()`、`RANK()` 或 `DENSE_RANK()`）来实现。

假设你有如下表结构：

- `students` 表：存储学生信息
  - `student_id`
  - `name`
  - 其他字段...

- `scores` 表：存储学生成绩
  - `student_id`
  - `course_id`
  - `score`

---

### ✅ 查询目标：
对于每门课程，找出 **成绩第2名到第3名** 的学生信息及其成绩。

---

### ✅ SQL 查询语句（使用 `ROW_NUMBER()`）：

```sql
SELECT 
    s.student_id,
    s.name,
    sc.course_id,
    sc.score
FROM (
    SELECT 
        sc.student_id,
        sc.course_id,
        sc.score,
        ROW_NUMBER() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) AS rank
    FROM scores sc
) AS ranked_scores
JOIN students s ON ranked_scores.student_id = s.student_id
WHERE ranked_scores.rank BETWEEN 2 AND 3;
```

---

### 🔍 解释：

- `ROW_NUMBER() OVER (PARTITION BY course_id ORDER BY score DESC)`：按课程分组，按成绩降序排列，为每个学生分配一个排名。
- `rank BETWEEN 2 AND 3`：筛选出第2名和第3名的学生。
- 最后通过 `JOIN` 连接 `students` 表获取学生姓名等信息。

---

### 📌 注意事项：

- 如果有多个学生分数相同，`ROW_NUMBER()` 会分配不同的排名，而 `RANK()` 和 `DENSE_RANK()` 会处理并列情况。
- 如果希望保留并列排名（例如：如果两名学生并列第1名，那么第2名是下一名），可以使用 `RANK()` 或 `DENSE_RANK()` 替代 `ROW_NUMBER()`。

---

### ✅ 示例：使用 `RANK()` 处理并列

```sql
SELECT 
    s.student_id,
    s.name,
    sc.course_id,
    sc.score
FROM (
    SELECT 
        sc.student_id,
        sc.course_id,
        sc.score,
        RANK() OVER (PARTITION BY sc.course_id ORDER BY sc.score DESC) AS rank
    FROM scores sc
) AS ranked_scores
JOIN students s ON ranked_scores.student_id = s.student_id
WHERE ranked_scores.rank BETWEEN 2 AND 3;
```

---

如需我根据你的实际表结构调整 SQL，请提供表结构和字段名。