plsql - PL/SQL 案例语句 - IS NULL

Question

我有一些 SQL，太多了，无法在这个问题中添加所有语句，我正在努力改进以消除系统速度下降。

在 PL/SQL 的一部分中，有一个 AND Exists 子句，其中包含一个 AND Not Exists 子句。

这两个子句都包含Case 语句。在 case 语句的 End 部分之后，有一个 IS NOT NULL 或 IS NULL 语句。使用 PL/SQL 多年以来，这是我第一次看到这种语法，我承认不理解它。整个 AND Exists 子句显示在下面，我们将不胜感激。

AND EXISTS  (
  SELECT Distinct PROJECTID
  From Milestone_History MH, 
       Project_Milestone PM
  Where MH.MilestoneId = PM.ID
    AND MH.Projectid = PR.ID
    AND ((UPPER(PM.Description) = 'FINAL BUILD INVOICE INSTRUCTIONS ISSUED') OR
         (UPPER(PM.Description) = 'FEASIBILITY STUDY REQUIRED') OR
         (UPPER(PM.Description) = 'PROJECT COMPLETE'))
     AND (case when UPPER(PM.Description) = 'FEASIBILITY STUDY REQUIRED' then
                  Actual
                End IS NOT NULL 

                AND 
                  case
                    when UPPER(PM.Description) = 'PROJECT COMPLETE' then
                      MH.Actual
                  end IS NULL)
           AND NOT EXISTS
         (SELECT Distinct MH2.ProjectID
          From Milestone_History MH2, Project_Milestone PM2
          Where MH2.MilestoneId = PM2.ID
            And MH2.ProjectID = MH.ProjectID
            AND case
                    when UPPER(PM2.Description) = 'PROJECT COMPLETE' then  -
                          MH2.Actual
                    end IS NOT NULL))

Answer 1

将 CASE...END 视为单个表达式。您可以使用括号来更好地理解:

(case when UPPER(PM.Description) = 'FEASIBILITY STUDY REQUIRED' 
    then Actual
 End) IS NOT NULL

当且仅当 CASE 找到匹配且 Actual 不为空时，此特定表达式才为 NOT NULL。

这可以重写为:

(UPPER(pm.description) = 'FEASIBILITY STUDY REQUIRED'
AND actual IS NOT NULL)

下面的语句可以重写为:

(UPPER(PM.Description) != 'PROJECT COMPLETE'
 OR PM.Description IS NULL
 OR actual IS NULL)

这两个语句显然是重叠的:Descrition 只能有一个值。当我们合并它们时，剩下的不多了:

(SELECT Distinct PROJECTID
   From Milestone_History MH, 
        Project_Milestone PM
  Where MH.MilestoneId = PM.ID
    AND MH.Projectid = PR.ID
    AND UPPER(PM.Description) = 'FEASIBILITY STUDY REQUIRED'
    AND actual IS NOT NULL
    AND NOT EXISTS
         (SELECT Distinct MH2.ProjectID
            From Milestone_History MH2, Project_Milestone PM2
           Where MH2.MilestoneId = PM2.ID
             And MH2.ProjectID = MH.ProjectID
             AND PM2.Description = 'PROJECT COMPLETE'
             AND MH2.Actual IS NOT NULL)
)

我认为当只有一个 case 时使用 CASE 有点误导。

要么作者对 CASE 的行为有错误的了解，要么这是一直更新但未清理的残余代码。