时间戳

首页 > 解决方案 > 根据条件消除 SQL 行

sql - 根据条件消除 SQL 行

问题描述

SELECT g.new_attempt, c.fullname, c.new_intaketermidname
    , g.new_module, c.new_programmeidname, g.statecodename
    , cp.new_termidname, c.statuscodename, g.new_resultstatusname, g.statuscodename    
FROM [NUP_MSCRM].[dbo].[FilteredNew_grade] g 
join [NUP_MSCRM].[dbo].New_studentprogress sp on sp.New_studentprogressId = g.new_progressid 
join [NUP_MSCRM].[dbo].FilteredContact c on c.contactid = sp.New_ContactId
join [NUP_MSCRM].[dbo].[FilteredNew_coursesofprogramme] cp on cp.new_coursesofprogrammeid = g.new_courseid
join [NUP_MSCRM].[dbo].FilteredNew_programme p on p.new_programmeid = c.new_programmeid    
where c.statecodename = 'Active' 
and c.statuscodename not in ('Alumni (active)')
and c.fullname = 'Stefanos Chalvatzis'

这会带来该特定学生的所有成绩（失败，通过），我想做的只是获得该学生的重考，但如果该学生在学期期间的任何时间通过了该课程，则省略重考行和通过的行，有没有办法做到这一点？

例如：

new_attempt fullname    new_intaketermidname    new_module  new_programmeidname statecodename   new_termidname  statuscodename  new_resultstatusname    statuscodename
1   Stefanos Chalvatzis 2017-18 / Fall  ΝΟΜ229-GR   LLB Law - Greek Law Inactive    2018-19 / Spring    Registered  Fail    Resit coursework & exam
2   Stefanos Chalvatzis 2017-18 / Fall  ΝΟΜ229-GR   LLB Law - Greek Law Inactive    2018-19 / Spring    Registered  Pass    Completed

正如您在此处看到的，该学生在 NOM229 中进行了重考，但在第二次尝试中通过了该课程 - 如果上述情况属于这种情况，我希望我的查询省略这两行，否则仅显示重考（如果找不到该课程为后来通过）。

标签： sqlsql-server

解决方案

以下适用于您提供的示例，使用 CTE 您可以检查pass结果是否存在，如果存在，则不返回该学生的结果。

WITH cte AS (
    SELECT g.new_attempt, c.fullname, c.new_intaketermidname
        , g.new_module, c.new_programmeidname, g.statecodename
        , cp.new_termidname, c.statuscodename, g.new_resultstatusname, g.statuscodename, c.new_studentid    
    FROM [NUP_MSCRM].[dbo].[FilteredNew_grade] g 
    join [NUP_MSCRM].[dbo].New_studentprogress sp on sp.New_studentprogressId = g.new_progressid 
    join [NUP_MSCRM].[dbo].FilteredContact c on c.contactid = sp.New_ContactId
    join [NUP_MSCRM].[dbo].[FilteredNew_coursesofprogramme] cp on cp.new_coursesofprogrammeid = g.new_courseid
    join [NUP_MSCRM].[dbo].FilteredNew_programme p on p.new_programmeid = c.new_programmeid    
    where c.statecodename = 'Active' 
    and c.statuscodename not in ('Alumni (active)')
    --and c.fullname = 'Stefanos Chalvatzis'
)
SELECT *
FROM cte C1
WHERE NOT EXISTS (
    SELECT 1
    FROM cte C2
    WHERE C2.statuscodename = 'Pass'
    AND C2.new_studentid = C1.new_studentid
);

推荐阅读