mysql - 原始 SQL 查询中 IF() 函数中的 CASE

Question

我有一个 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() 表达式中，我必须检查另一个变量的值。如果该值为真，则查询应按原样运行，如果不是，则为 #{ticket_variable}.convenience_charge_amount + #{ticket_variable}.our_convenience_charge_amount 和 #{ticket_variable}.our_convenience_charge_amount 的值 取0。任何帮助表示赞赏。

Answer 1

您可以嵌套 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),
...