我正在尝试选择查询中的案例,并想使用该案例在同一查询中生成的数据列。
我的查询是:
select order_id , order_item_id , sku ,merchant_payable as "Value Get" ,
case when name like 'Rise%'
then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Masha%'
then amount-(((amount*9.10)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Bboy%'
then amount-(((amount*14.17)/100)+ ((amount*3.08)/100) + 51.30)
end as "Value Should Get" ,
"Value Should Get" - merchant_payable
from meta.paytm_payment as ppo
where
case when name like 'Rise%'
then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Masha%'
then amount-(((amount*9.10)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Bboy%'
then amount-(((amount*14.17)/100)+ ((amount*3.08)/100) + 51.30)
end > merchant_payable
order by order_created_at ;
您可以使用来干燥投影的重复项CTE
,然后在WHERE
谓词中使用它:
WITH myCte AS
(
select order_id , order_item_id , sku ,merchant_payable, order_created_at ,
case when name like 'Rise%'
then amount-(((amount*12.14)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Masha%'
then amount-(((amount*9.10)/100)+ ((amount*3.08)/100) + 51.30)
when name like 'Bboy%'
then amount-(((amount*14.17)/100)+ ((amount*3.08)/100) + 51.30)
end as "Value Should Get"
from paytm_payment as ppo
)
SELECT *, "Value Should Get" - merchant_payable AS "Nett"
from myCte
where "Value Should Get" > merchant_payable
order by order_created_at;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句