原始SQL查询中IF（）函数内部的CASE

尼安塔

我有一个SQL查询，该查询已经使用IF（）函数根据返回的各个表达式检查某些条件。我必须在IF（）中添加另一个条件（这将是另一个“和”），这将影响两个表达式的属性。查询是

select 
  #{booking_variable}.travel_id as operator_id,
  round(sum(#{ticket_variable}.customer_commission), 2) as commission,
  IF(
    (#{booking_variable}.is_online = #{AdminType::YES} 
      and #{booking_variable}.is_ts_payment = #{AdminType::YES}),
    round(sum(-#{ticket_variable}.customer_commission
      - #{ticket_variable}.convenience_charge_amount
      + #{ticket_variable}.our_convenience_charge_amount), 2),
    round(sum(#{ticket_variable}.adult_fare 
      - #{ticket_variable}.customer_commission
      + #{ticket_variable}.service_tax_amount
      + #{ticket_variable}.our_convenience_charge_amount 
      - #{ticket_variable}.offer_discount), 2)
  ) as amount_to_be_paid,
  round(sum(#{booking_variable}.total_fare)) as total_fare,
  0 as cancel_fare,
  round(sum(#{ticket_variable}.adult_fare 
      + #{ticket_variable}.service_tax_amount
      - #{ticket_variable}.offer_discount )) as net_amount,
  #{booking_variable}.travel_name as operator_name,
  ROUND(sum(#{ticket_variable}.service_tax_amount), 2) as total_service_tax,
  ROUND(sum(#{ticket_variable}.convenience_charge_amount), 2) as total_convenience_charge_amount,
  ROUND(sum(#{ticket_variable}.our_convenience_charge_amount), 2) as total_our_convenience_charge_amount,
  0 as cancelled_convenience_charge_amount,
  (select users.branch_id from users where id=#{ticket_variable}.booked_by) as travel_branch_id
from #{booking_variable} use index(index_#{booking_variable}_travel_date), 
  #{ticket_variable}
where #{conditions1[0]} 
group by travel_branch_id, operator_id

在这里，在IF（）表达式中，我必须检查另一个变量的value。如果该值为true，则查询应按原样运行，如果不正确，则应将#{ticket_variable}.convenience_charge_amount + #{ticket_variable}.our_convenience_charge_amountand的值#{ticket_variable}.our_convenience_charge_amount视为0。感谢任何帮助。

比尔·卡温

您可以嵌套IF()表达式。

如果您需要部分计算是有条件的，则可以使用IF()。

...
  IF(
    (#{booking_variable}.is_online = #{AdminType::YES} 
      and #{booking_variable}.is_ts_payment = #{AdminType::YES}),
    round(sum(-#{ticket_variable}.customer_commission
      - IF((...some other condition...),
        #{ticket_variable}.convenience_charge_amount
        + #{ticket_variable}.our_convenience_charge_amount,
        0)
      ), 2),
...

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。