gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 22:03:11 27 4
gpt4 key购买 nike

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

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

这两个子句都包含Case 语句。在 case 语句的 End 部分之后,有一个 IS NOT NULLIS 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))

最佳答案

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 的行为有错误的了解,要么这是一直更新但未清理的残余代码。

关于plsql - PL/SQL 案例语句 - IS NULL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16547888/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com