mysql - 如何在单个查询中比较两个表的总和？

Question

http://sqlfiddle.com/#!2/fae8a/6

我有三个表 -- orders、order_line_items、order_payments。

我可以使用 select 语句汇总订单的总计和付款，但我似乎无法找出正确的语法来仅返回“付款”加起来小于“总计”的订单(订单未全额支付)。

我错过了什么？

Answer 1

你需要使用HAVING子句来通过聚合函数过滤结果，你还需要使用COALESCE()来过滤掉null

select 
    `orders`.`id`, 
    `order_payments`.`order_id`, 
    `order_payments`.`amount`, 
    `order_line_items`.`order_id`, 
    `order_line_items`.`price_each`, 
    `order_line_items`.`quantity`,
    COALESCE(SUM(`order_line_items`.`price_each` * `order_line_items`.`quantity`),0) as `order_total`,
    COALESCE(SUM(`order_payments`.`amount`),0) as `payments`
from 
    `orders` 
left join 
    `order_payments` on `order_payments`.`order_id` = `orders`.`id` 
left join 
    `order_line_items` on `order_line_items`.`order_id` = `orders`.`id`
group by 
    `orders`.`id`
HAVING   `payments` < `order_total`

Updated fiddle

根据评论编辑

另一种重写查询的方法是使用子查询你之前的查询是给你笛卡尔积

select 
    `o`.`id`,
(SELECT COALESCE(SUM(`price_each` * `quantity`),0) FROM `order_line_items` WHERE order_id =`o`.`id` ) as `order_total`,
(SELECT COALESCE(SUM(`amount`),0) FROM `order_payments` WHERE order_id =`o`.`id`) as `payments`
from 
    `orders` o
HAVING   `payments` < `order_total`

Without Having Clause

With Having Clause