通过SELECT在MySQL中进行数学运算 [英] Do math in MySQL from SELECT

问题描述

我有这样的MySQL查询

I have MySQL query like this

SELECT `transaction`.id,
CONCAT(contact.`name`, " ", contact.last_name) as fullName,
(SELECT SUM(total) FROM transaction_product WHERE transaction_product.ref_transaction = `transaction`.id) as subtotal,
    (SELECT SUM(transaction_payment.idr_cash + transaction_payment.idr_bni + transaction_payment.idr_ame_exp + transaction_payment.idr_cc_niaga) FROM transaction_payment WHERE transaction_payment.`transaction` = `transaction`.id ) AS payment,
    (subtotal - payment) as balance
FROM `transaction`
LEFT JOIN contact
    ON contact.id = `transaction`.contact_id
WHERE reservation_status = 3
    AND `transaction`.id = 6345

如您所见，我想获得SELECT中数学运算的平衡. (subtotal - payment) as balance
我该怎么办?

As you see, I want to get the balance which that math come in SELECT. (subtotal - payment) as balance
how should I do that?

推荐答案

最简单的方法可能是将初始结果用作子查询，因此:

The simplest way is probably to use your initial result as a subquery, hence:

SELECT          id,
                fullName,
                subtotal,
                payment,
                (subtotal-payment) as balance

FROM                     
(
  SELECT `transaction`.id, 
      CONCAT(contact.`name`, " ", contact.last_name) as fullName, 
      (SELECT SUM(total) FROM transaction_product WHERE transaction_product.ref_transaction = `transaction`.id) as subtotal, 
      (SELECT SUM(transaction_payment.idr_cash + transaction_payment.idr_bni + transaction_payment.idr_ame_exp + transaction_payment.idr_cc_niaga) FROM transaction_payment WHERE transaction_payment.`transaction` = `transaction`.id ) AS payment
  FROM `transaction` 
  LEFT JOIN contact 
      ON contact.id = `transaction`.contact_id 
  WHERE reservation_status = 3 
      AND `transaction`.id = 6345
  )

这篇关于通过SELECT在MySQL中进行数学运算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！