如何增加根据一组条件变化的折扣栏？

Question

我想添加折扣列，它根据帐户类型和服务的不同而不同。任何表中都不存在折扣列。

我希望在运行时有一个列折扣列，而折扣根据Resller_name、Account_type和服务的不同而不同。

如何添加折扣栏？

此外，我需要添加计算成本列与以下公式: a.list_cost*(1-d.discount)。我尝试创建了一个表折扣，并添加了异常值，并创建了以下脚本：

select a.account, a.name, b.Resller_Name, b.bcn, 
b.Account_Type as "Internal\reseller", a.service, a.list_cost "cost of service",
d.discount, a.list_cost*(1-d.discount) as "Calculated Cost" 
from imtest.cloudchckr_test_full a, imtest.master_info_test_full b, imtest.discount d 
where a.account=b.AWS_id(+) and b.Account_type=d.account_type; 

但它只在帐户类型的基础上显示折扣。

​

​

Answer 1

您可以在折扣表中添加order is或在折扣表中添加任何唯一的Id，然后连接所有表并获取折扣金额。与折扣表进行左连接，如果数据不存在，则会得到空值。

Answer 2

select 
   a.account, 
   a.name, 
   b.Resller_Name, 
   b.bcn, 
   b.Account_Type as "Internal\reseller", 
   a.service, 
   a.list_cost "cost of service", 
   d.discount, 
   a.list_cost*(1-d.discount) as "Calculated Cost" 
from imtest.cloudchckr_test_full a, 
   imtest.master_info_test_full b, 
   imtest.discount d 
where 
   a.account=b.AWS_id(+) 
   and (d.account_type is null or b.Account_type=d.account_type)
   and (d.Resller_Name is null or b.Resller_Name=d.Resller_Name)
   and (d.service is null or a.service=d.service)
UNION ALL
select 
   a.account, 
   a.name, 
   b.Resller_Name, 
   b.bcn, 
   b.Account_Type as "Internal\reseller", 
   a.service, 
   a.list_cost "cost of service", 
   0, 
   a.list_cost as "Calculated Cost" 
from imtest.cloudchckr_test_full a, 
   imtest.master_info_test_full b
where 
   a.account=b.AWS_id(+) 
   and not exists 
        (SELECT 1 
             FROM imtest.discount d 
          WHERE (d.account_type is null or b.Account_type=d.account_type)
            and (d.Resller_Name is null or b.Resller_Name=d.Resller_Name)
            and (d.service is null or a.service=d.service)
         )