如何用SQL实现查询指定学生未参加的指定测验？

嘿，我来帮你解决这个查询问题，先理清楚需求：我们要根据传入的学生ID和测验ID，找出该学生未参加的指定测验；同时还要处理当student_id为空时的情况——也就是查询指定测验是否没有任何学生参加对吧？

先放测试用的临时表代码 #

先把你提供的可运行测试代码贴出来，方便复现场景：

create table #student ( id int identity(1,1), firstname varchar(50), lastname varchar(50) )
create table #quiz ( id int identity(1,1), quiz_name varchar(50) )
create table #quiz_details ( id int identity(1,1), quiz_id int, student_id int )
insert into #student(firstname, lastname) values ('LeBron', 'James'), ('Stephen', 'Curry')
insert into #quiz(quiz_name) values('NBA 50 Greatest Player Quiz'), ('NBA Top 10 3 point shooters')
insert into #quiz_details(quiz_id, student_id) values (1, 2), (2, 1)
-- drop table #student
-- drop table #quiz
-- drop table #quiz_details

核心问题分析 #

你之前用了INNER JOIN，但内连接只会返回所有表都有匹配的记录，而我们需要的是没有匹配的情况——也就是某学生没参加某测验，或者某测验根本没人参加。所以得换用LEFT JOIN或者NOT EXISTS的逻辑。

解决方案1：用LEFT JOIN实现（灵活处理参数） #

这个方案可以同时兼容student_id有值和为空的情况，直接看代码：

-- 先定义参数，实际使用时可以换成存储过程参数
DECLARE @student_id int, @quiz_id int
-- 测试案例1：查询LeBron（id=1）未参加的测验1
SET @student_id = 1
SET @quiz_id = 1

SELECT 
    Q.id,
    Q.quiz_name,
    S.firstname,
    S.lastname
FROM #quiz Q
-- 左连接测验详情，只匹配指定学生（如果student_id不为空）
LEFT JOIN #quiz_details QD 
    ON Q.id = QD.quiz_id 
    AND (@student_id IS NULL OR QD.student_id = @student_id)
-- 左连接学生表，没匹配到的话学生字段就是NULL
LEFT JOIN #student S 
    ON S.id = QD.student_id
WHERE 
    -- 先锁定要查询的测验
    Q.id = @quiz_id
    AND (
        -- 情况1：指定学生没参加该测验（详情表无匹配）
        (@student_id IS NOT NULL AND QD.id IS NULL)
        -- 情况2：student_id为空，且该测验无任何学生参加
        OR (@student_id IS NULL AND QD.id IS NULL)
    )

运行这个代码，测试案例1会返回你预期的结果：

id quiz_name                          firstname lastname
--- ---------------------------------- --------- --------
1  NBA 50 Greatest Player Quiz        NULL      NULL

解决方案2：用NOT EXISTS实现（逻辑更直观） #

如果你觉得左连接的逻辑有点绕，用NOT EXISTS会更直接——直接检查是否不存在对应的参与记录：

DECLARE @student_id int, @quiz_id int
SET @student_id = 1
SET @quiz_id = 1

SELECT 
    Q.id,
    Q.quiz_name,
    NULL AS firstname,
    NULL AS lastname
FROM #quiz Q
WHERE 
    Q.id = @quiz_id
    AND (
        -- 指定学生未参加该测验
        (@student_id IS NOT NULL AND NOT EXISTS (
            SELECT 1 FROM #quiz_details QD 
            WHERE QD.quiz_id = Q.id AND QD.student_id = @student_id
        ))
        -- student_id为空，且该测验无任何参与者
        OR (@student_id IS NULL AND NOT EXISTS (
            SELECT 1 FROM #quiz_details QD 
            WHERE QD.quiz_id = Q.id
        ))
    )

这个方案的结果和上面完全一致，而且逻辑更清晰：只要不存在对应的参与记录，就返回该测验的信息，学生字段直接设为NULL。

补充说明 #

• 当@student_id为空时，这个查询会返回指定测验是否没有任何学生参加，如果有学生参加就不会返回结果。
• 如果需要返回所有未被指定学生参加的测验（而不是单个指定测验），只要去掉Q.id = @quiz_id的条件即可。

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