sql - 在 SELECT 中添加逻辑

Question

请注意在下面的查询中，括号内的前两个查询，我添加了两个重复的查询，我确信这不是一个好的做法。我需要随时在需要值的地方重复此查询。

SQL Server 抛出一个关于不在 SELECT 关键字内写入 DECLARE 的异常。我可以做什么或我缺少什么来重构它？

SELECT A.StudentId,
(
    CASE WHEN (SELECT B.OverwrittenScore
        FROM dbo.OverwrittenScores AS B
        WHERE B.StudentId = A.StudentId 
        AND B.AssignmentId = @assignmentId
        ) IS NOT NULL
            THEN (
                SELECT B.OverwrittenScore
                FROM dbo.OverwrittenScores AS B
                WHERE B.StudentId = A.StudentId 
                AND B.AssignmentId = @assignmentId)
            ELSE (-- ANOTHER QUERY, BY THE MOMENT: SELECT 0 ) 
    END
) AS FinalScore
FROM dbo.Students AS A

Answer 1

我的建议是考虑使用 JOIN:

SELECT A.StudentId,
    case 
        when B.OverwrittenScore is not null
        then B.OverwrittenScore
        else 0 
    end AS FinalScore
FROM dbo.Students AS A
LEFT JOIN dbo.OverwrittenScores B
    ON B.StudentId = A.StudentId 
    AND B.AssignmentId = @assignmentId

如果您想在 else 中使用另一个选择，则可以根据需要添加更多连接:

SELECT A.StudentId,
    case 
        when B.OverwrittenScore is not null
        then B.OverwrittenScore
        else c.whatever
    end AS FinalScore
FROM dbo.Students AS A
LEFT JOIN dbo.OverwrittenScores B
    ON B.StudentId = A.StudentId 
    AND B.AssignmentId = @assignmentId
LEFT JOIN anothertable c
   ON a.col = c.col

或者您甚至可以使用 COALESCE 来替换空值:

SELECT A.StudentId,
    coalesce(B.OverwrittenScore, 0) as FinalScore 
FROM dbo.Students AS A
LEFT JOIN dbo.OverwrittenScores B
    ON B.StudentId = A.StudentId 
    AND B.AssignmentId = @assignmentId