MySQL 计算过选择不适用于空子选择

Question

我试图通过一个 SQL 查询来获取 5 个不同表中结果的总和。我有 5 个表，每个表可能包含属于订单的记录。 5 个表中所有记录的总和决定了总订单价格。

在这个论坛上搜索我提出了以下查询:

SELECT
  OrderTotal.total + Shipping.amount + Service.amount - Gift.amount - RMA.total as TotalCosts
FROM
  (SELECT SUM(price * amount) AS total FROM order WHERE order_id=123456) OrderTotal
  (SELECT amount FROM shipping WHERE order_id=123456) Shipping,
  (SELECT amount FROM service WHERE order_id=123456) Service,
  (SELECT SUM(price * amount) AS total FROM rma WHERE order_id=123456) RMA,
  (SELECT amount FROM gift WHERE order_id=123456) Gift

我现在面临的问题是，例如当最后一个 SELECT 返回 0 行时，总的 TotalCosts 没有返回。

如何解决？

Answer 1

使用 UNION 怎么样？

SELECT  order_id, SUM(total) GrandTotal
FROM
        (
            SELECT  order_id, SUM(price * amount) AS total 
            FROM    `order` 
            WHERE   order_id = 123456
            UNION ALL
            SELECT  order_id, amount AS total 
            FROM    shipping 
            WHERE   order_id = 123456
            UNION ALL
            SELECT  order_id, amount AS total 
            FROM    service 
            WHERE order_id = 123456
            UNION ALL
            SELECT  order_id, SUM(price * amount) AS total 
            FROM    rma 
            WHERE   order_id = 123456
            UNION ALL
            SELECT  order_id, amount AS total 
            FROM    gift 
            WHERE   order_id=123456
        ) subTable
GROUP   BY order_id