case语句

beq87vna 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(278)

我有这样的要求，我必须模拟一个excel公式到oraclesql中。由于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

有人能帮忙吗！

sql oracle

来源：https://stackoverflow.com/questions/62291948/case-statement-with-multiple-nested-else

1条答案

按热度按时间

gmol16391#

你把逻辑换成了 LIKE 在某些地方。因为您没有包含任何数据，所以无法确定这是否导致了您报告的某些问题，但我已恢复了与excel公式中使用的单个值的比较。此外，我认为您对“业务回顾<>0”和“业务状况<>‘事后回顾’”分支机构的解释可能有误。
以下操作应满足您的要求：

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

在将来，您可能需要对数据进行后期测试-创建表语句和插入语句以将数据放入这些表中是一个好主意-以及预期的结果。

赞(0）回复(0）举报 2021-07-29

我来回答

case语句

1条答案

相关问题

热门标签

最新问答