MySQL 查询仅在行存在时返回值

Question

我有以下完美运行的查询，但前提是每个选择都找到一行。

如果没有找到行，我尝试添加 IFNULL 以返回 0，但我仍然没有得到正确的返回值。

SELECT IFNULL(paid_value,0)-IFNULL(ordered_value,0)+IFNULL(credit_value,0) AS account_balance
            FROM
            (
              SELECT customer_id, SUM(order_total) AS ordered_value
              FROM orders
              WHERE customer_id = '1'
              GROUP BY customer_id
            ) AS orders
            LEFT JOIN
            (
              SELECT customer_id, SUM(amount) AS paid_value
              FROM transactions
              WHERE customer_id = '1'
              GROUP BY customer_id
            ) as payments
            ON orders.customer_id = payments.customer_id
            LEFT JOIN
            (
                SELECT customer_id, SUM(amount) AS credit_value
                FROM credits
                WHERE customer_id = '1'
                GROUP BY customer_id
            ) as credits
            ON orders.customer_id = credits.customer_id

此查询当前返回空，它不返回 NULL 或 0。

当我运行时

              SELECT customer_id, SUM(order_total) AS ordered_value
              FROM orders
              WHERE customer_id = '1'
              GROUP BY customer_id

它也返回空，而不是 NULL 或 0，除非有一行。为了使完整查询正常工作，3 个单独的查询中的每一个都需要有一行。

有什么想法吗？

Answer 1

如果因为没有列有结果集，所以返回一个空结果集，如果你想在任何情况下总是显示一行，你可以尝试一些像这样的技巧，例如:

SELECT IFNULL(SUM(payments.paid_value),0)-IFNULL(SUM(orders .ordered_value),0)+IFNULL(SUM(credits.credit_value),0) AS account_balance
FROM 
(SELECT 1 AS idx, 0 AS paid_value, 0 AS ordered_value, 0 AS credit_value) a
LEFT JOIN
            (
              SELECT 1 AS idx, customer_id, SUM(order_total) AS ordered_value
              FROM orders
              WHERE customer_id = '1'
              GROUP BY customer_id
            ) AS orders
            ON a.idx = orders.idx 
            LEFT JOIN
            (
              SELECT customer_id, SUM(amount) AS paid_value
              FROM transactions
              WHERE customer_id = '1'
              GROUP BY customer_id
            ) as payments
            ON orders.customer_id = payments.customer_id
            LEFT JOIN
            (
                SELECT customer_id, SUM(amount) AS credit_value
                FROM credits
                WHERE customer_id = '1'
                GROUP BY customer_id
            ) as credits
            ON orders.customer_id = credits.customer_id
            GROUP BY a.idx

该示例是一个概念证明，甚至可以适用于您需要始终返回具有默认值的行的其他情况，即使基础表中没有元素也是如此。