我有这个要求，我必须将Excel公式模拟到Oracle SQL中。由于Excel非常简陋，因此所有内容都包含在其中，但是SQL是有条不紊的，必须符合要求。
这是Excel(业务类别(中的公式：
=IF([@[Order Days]]="","",
IF(OR([@business type]="Probation Touch Point",[@business type]="Referral",
[@business type]="Mystery Shopping"),IF([@[Order Days]]>45,"Allowed","Not Allowed"),
IF(OR(AND([@business status]="Assigned",[@[Order Days]]>60),
AND([@business status]="Pre-Review",[@[Order Days]]>60),
AND([@business status]="In-Review",[@[site]]="Location",[@[Order Days]]>44),
AND([@business status]="In-Review",[@[site]]="Headquarters",[@[Order Days]]>74)),"Permitted",
IF([@business status]="Post-Review",IF([@business review]=0,IF([@[Order Days]]>44,"Allowed","Not
Allowed"),
IF(OR(AND([@[site]]="Headquarters",[@[Order Days]]>90),
AND([@[site]]="Location",[@[Order Days]]>60)),"Allowed","Not Allowed")),"Not Allowed"))))
正如其引人注目的那样，单个公式中有多个else及其目的。
我的SQL公式：
case when order_days is null then null
when business_type like '%Touch%' or business_type = 'Referral'
or business_type like 'Myster%' and order_days > 45 then 'Allowed'
when (business_status = 'Assigned' and order_days > 60) or
(business_status = 'Pre-Review' and order_days > 60) or
(business_status = 'In-Review' and site like 'Loca%' and order_days > 44) or
(business_status = 'In-Review' and site like 'Head%' and order_days > 74) then 'Permitted'
when (business_status like 'Post%' or business_type = 0 or order_days > 44) then 'Allowed'
when (site like 'Head%' and order_days > 90) then 'Allowed'
when (site like 'Loca%' and order_days > 60) then 'Allowed' else 'Not Allowed'
end as business_category
sql案是徒劳的，因为它导致了错误的数字。
Excel结果：
Business Category Count of Rows
Allowed 130
Not Allowed 1122
Permitted 200
SQL 结果：
business_category Count
Allowed 980
Not Allowed 272
Permitted 200
有人可以提供帮助吗！
您在某些地方切换了逻辑以使用LIKE。由于您没有包含任何数据，因此无法确定这是否导致了您报告的某些问题，但我已将比较恢复到 Excel 公式中使用的单个值。此外，我认为您对"business_review <> 0"和"business_status <>'后审查'"分支的解释可能是错误的。
以下内容应执行所需的操作：
case
when order_days is null
then null
when business_type in ('Probation Touch Point', 'Referral', 'Mystery Shopping') AND order_days > 45
then 'Allowed'
when (business_status = 'Assigned' and order_days > 60) or
(business_status = 'Pre-Review' and order_days > 60) or
(business_status = 'In-Review' and site = 'Location' and order_days > 44) or
(business_status = 'In-Review' and site = 'Headquarters' and order_days > 74) then 'Permitted'
when (business_status = 'Post-Review' and business_review = 0 and order_days > 44) then 'Allowed'
when (business_status = 'Post-Review' and business_review = 0 and order_days <= 44) then 'Not Allowed'
when (business_status = 'Post-Review' and business_review <> 0 and ((site = 'Headquarters' and order_days > 90) or
(site = 'Location' and order_days > 60))
then 'Allowed'
else 'Not Allowed'
when business_status <> 'Post-Review' then 'Not Allowed'
end as business_category
将来，您可能希望发布测试数据 - CREATE TABLE 语句和 INSERT 语句将数据放入这些表中是一个好主意 - 以及预期的结果。