如何查询选修同一课程的学生？现有SQL查询存在问题求修正

原查询的直接问题 #

你写的SQL里存在笔误：i.lesson_if应该是i.lesson_id，这个拼写错误会导致查询无法匹配正确的课程ID，直接影响结果输出。

修正后的基础查询（找出有其他学生同修课程的记录） #

修正笔误后，这个查询可以筛选出所有存在其他学生同修的课程记录：

select * 
from lesson e
where exists (
    select 1 
    from lesson i
    where e.lesson_id = i.lesson_id 
      and e.student_id <> i.student_id
)
order by lesson_id;

注：select 1和select null功能完全一致，但前者更符合SQL编写的通用习惯。

直观展示同一课程的其他学生（关联同课学生） #

如果需要直接看到每个学生对应的同课其他学生，可使用自连接查询：

select 
    e.student_id as 当前学生ID,
    e.lesson_id as 课程ID,
    i.student_id as 同课其他学生ID
from lesson e
inner join lesson i 
    on e.lesson_id = i.lesson_id 
    and e.student_id <> i.student_id
order by e.lesson_id, e.student_id;

为每个学生标记是否有其他学生同修 #

如果需要明确为每条学生选课记录标记「是否存在其他学生同修」，可以用窗口函数实现：

select 
    student_id as 学生ID,
    lesson_id as 课程ID,
    case 
        when count(student_id) over (partition by lesson_id) > 1 
        then '有其他学生选修' 
        else '无其他学生选修' 
    end as 同课状态
from lesson
order by lesson_id;

如果只需要保留「有其他学生同修」的记录，可嵌套子查询过滤：

select *
from (
    select 
        student_id as 学生ID,
        lesson_id as 课程ID,
        case 
            when count(student_id) over (partition by lesson_id) > 1 
            then '有其他学生选修' 
            else '无其他学生选修' 
        end as 同课状态
    from lesson
) t
where 同课状态 = '有其他学生选修'
order by lesson_id;

内容的提问来源于stack exchange，提问作者Antonello Cioffi