我有两个表。假设它们是:
table customers: id, name
table orders: id, customer_id, is_finished(boolean), order_sum
我想加入以获取客户名称、他/她完成的订单数量以及他/她在中总共花费的金额
所以我做了这个SQL查询:
SELECT c.name,
coalesce(count(o.id), 0) AS orders_count,
coalesce(sum(o.order_sum ), 0) AS total_spent
FROM customers c
LEFT OUTER JOIN orders o on c.id = o.user_id
WHERE c.id = customer_id
AND o.is_finished = true
GROUP BY c.name;
如果客户有任何已完成的订单,那么它会把一切都做好。
|'John'|3|150|
但如果没有订单或有订单没有完成,它将不会输出任何东西。但我想要的输出如下:
|'John'|0|0|
那么,在客户没有订单的情况下,进行这样的查询以获得结果的正确方法是什么呢?
我正在使用postgreSQL btw
只需将LEFT JOIN
ed表上的条件从WHERE
子句移动到LEFT JOIN
的ON
子句,否则它将成为强制性的,并过滤掉没有匹配订单的客户:
SELECT c.name,
count(o.id) AS orders_count,
coalesce(sum(o.order_sum ), 0) AS total_spent
FROM customers c
LEFT OUTER JOIN orders o ON c.id = o.user_id AND o.is_finished = true
WHERE c.id = customer_id
GROUP BY c.id, c.name;