从 SELECT 在 MySQL 中进行数学运算

Do math in MySQL from SELECT

提问by GusDeCooL

I have MySQL query like this

我有这样的 MySQL 查询

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

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

如您所见，我想在 SELECT 中获得该数学的平衡。(subtotal - payment) as balance
我该怎么做？

回答by Mark Byers

You can't use aliases that are defined in the SELECT clause to calculate other columns that are also in the same SELECT clause. You have at least three options:

您不能使用 SELECT 子句中定义的别名来计算也在同一 SELECT 子句中的其他列。你至少有三个选择：

1. Repeat the subquery every time you need to use it. This is has the disavantage that you will need to repeat a lot of code. Since your subqueries are long and complex, this is an undesirable option.

2. Use a subquery and an outer query.

   SELECT
       *,
       (subtotal - payment) AS balance
   FROM
   (
        SELECT 
            ...,
            (...) AS subtotal,
            (...) AS payment
        FROM ... 
   ) T1
   

3. Use a JOIN instead of subselects. This is slightly more complex for your situation, but it will be better for performance if you ever need to fetch more than one row.

1. 每次需要使用它时都重复子查询。这样做的缺点是您需要重复大量代码。由于您的子查询又长又复杂，这是一个不可取的选择。

2. 使用子查询和外部查询。

   SELECT
       *,
       (subtotal - payment) AS balance
   FROM
   (
        SELECT 
            ...,
            (...) AS subtotal,
            (...) AS payment
        FROM ... 
   ) T1
   

3. 使用 JOIN 而不是子选择。这对于您的情况来说稍微复杂一些，但如果您需要获取多于一行，那么性能会更好。

回答by dash

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
  )