您可以将表与其自身连接起来，并为每个连接筛选特定的考试id。
例如（您需要用表名替换@students）：在下面的示例中，我为exam\u id=1创建了一个子查询，为exam\u id=2创建了第二个子查询，为exam\u id=3创建了第三个子查询。这将为您提供一个“新表”，然后您可以对其进行筛选。

SELECT
    E1.studentid,
    E1.Status AS [Status_exam2],
    E2.Status AS Status_exam2,
    E3.Status AS Status_exam3
FROM
(
    -- The first exam_id as the main table as normally all students will do the first exam
    SELECT *
    FROM @students
    Where exam_id = 1
) E1
LEFT JOIN
(
    -- Left join with the second selection as maybe not all patients already did the 
    -- second exam    
    SELECT *
    FROM @students
    Where exam_id = 2
) E2 ON E1.studentid = E2.studentid
LEFT JOIN
(
    SELECT *
    FROM @students
    Where exam_id = 3
) E3 ON E1.studentid = E3.studentid

在您的示例中，这将提供以下输出：

此“新”表，您可以筛选：例如：
通过id1检查但未进行第二次检查或第二次检查失败的患者在上述计划结束时添加以下内容：

WHERE E1.Status = 'Pass'
    AND E2.Status is null or E2.Status = 'Fail'

这将为您提供以下信息：

您似乎假设测试是按顺序进行的，并且只依赖于一个前提条件。这不是推荐的方法，但它是您需要的结果吗？

select Student_id, max(Exam_Id) as Last_Pass_Exam_Id 
from Tbl_StudentsExamMarking
where Status = 'Passed'
group by Student_id

假设我理解你的挑战，试着这样做：

-- Exam data mockup table and data.
DECLARE @ExamData TABLE ( Student_id INT, Class_id INT, Office_Id INT, Exam_Id INT, [Status] VARCHAR(4) );
INSERT INTO @ExamData ( Student_id, Class_id, Office_Id, Exam_Id, [Status] )
VALUES
    ( 1, 1, 1, 1, 'Pass' ),
    ( 2, 1, 1, 1, 'Pass' ),
    ( 9, 1, 1, 1, 'Fail' ),
    ( 10, 1, 1, 1, 'Fail' ),
    ( 1, 1, 1, 2, 'Pass' ),
    ( 2, 1, 1, 2, 'Fail' );

-- What exam has been passed?
DECLARE @PassedExamNo INT = 1;

-- Show students who have passed the specified exam and are eligible for the next.
-- Excludes:
--  Students who failed required exam.
--  Students who have already passed the next sequential exam.
--  Assumption: Exam ids are incremented by 1 for each sequential exam.

SELECT * FROM @ExamData AS e
WHERE
    Office_Id = 1
    AND Class_Id =1 
    AND Exam_Id = @PassedExamNo 
    AND [Status] = 'Pass'
    AND NOT EXISTS (
        SELECT * FROM @ExamData AS x WHERE 
            x.Office_Id = e.Office_Id AND x.Student_id = e.Student_id AND x.Exam_Id = ( e.Exam_Id + 1 ) AND x.[Status] = 'Pass'
    );

返回任何有资格参加下一次考试的学生：

+------------+----------+-----------+---------+--------+
| Student_id | Class_id | Office_Id | Exam_Id | Status |
+------------+----------+-----------+---------+--------+
|          2 |        1 |         1 |       1 | Pass   |
+------------+----------+-----------+---------+--------+

您可以在ssms中运行上述示例。

您可以尝试下面的查询一次。这将给每个学生和他参加的最后一次考试和状态

SELECT student_Id
    ,Exam_Id
    ,[Status]
FROM (
    SELECT student_Id
        ,Exam_Id
        ,[Status]
        ,ROW_NUMBER() OVER (
            PARTITION BY student_Id ORDER BY Exam_Id DESC
            ) [rownum]
    FROM Tbl_StudentsExamMarking
    WHERE Office_Id = 1
        AND Class_Id = 1
    ) a
WHERE a.[rownum] = 1

根据您的评论添加新查询。

DECLARE @exam_id INT = 2
SELECT
    student_Id
   ,Exam_Id
   ,[Status]
FROM Tbl_StudentsExamMarking
WHERE Office_Id = 1
AND Class_Id = 1
AND ((Exam_id = @exam_id AND [Status] = 'Fail')
   OR (Exam_id = @exam_id - 1 AND [Status] = 'Pass'))

查询1的示例输出

这是我的问题的完整答案。5天后我就决定了。
选择st.student\u id、st.student\u name、sm.status，sm.exam\u id from tbl\u studentsexammarking sm inner join tbl\u students st on st.student\u id=sm.student\u id where sm.office\u id=1 and sm.class\u id=1 and sm.exam\u id=2 and status='fail'and month（sm.exam\u date）=08且不存在（从tbl\u studentsexammarking as f中选择where f.student\u id=sm.student\u id and f.office\u id=sm.office\u id and f.status=“通过”和f.exam\u id=（sm.exam\u id+1））并且存在（从tbl\u studentsexammarking as f选择其中f.student\u id=sm.student\u id和f.office\u id=sm.office\u id和f.status=“通过”和f.exam\u id=（sm.exam\u id-1））并且不存在（从tbl\u studentsexammarking as f选择其中f.student\u id=sm.student\u id和f.office\u id=sm.office\u id和f.status=“pass”和f.exam\u id=（sm.exam\u id））--并且不存在（selectfrom tbl\u studentsexammarking as x where x.office\u id=sm.office\u id and x.student\u id=sm.student\u id and x.exam\u id=（sm.exam\u id）and x.[status]=”fail“）union选择st.student\u id，st.student\u name，sm.status，sm.exam\u id from tbl\u studentsexammarking sm inner join tbl\u students st on st.student\u id=sm.student\u id
其中sm.office\u id=1，sm.class\u id=1，sm.exam\u id=1，status='pass'和month（sm.exam\u date）=08
并且不存在（从tbl\u studentsexammarking as x选择其中x.office\u id=sm.office\u id和x.student\u id=sm.student\u id和x.exam\u id=（sm.exam\u id+1）和x.[状态]='通过'）并且不存在（从tbl\u studentsexammarking as x选择其中x.office\u id=sm.office\u id和x.student\u id=sm.student\u id和x.exam\u id=（sm.exam\u id+1）和x、 [状态]=“失败”）