mysql - LEFT JOIN - 为 payment_method 的总和返回带有左连接的空结果

Question

这是我关于 stackoverflow 的第一个问题，所以我尽量不搞砸。我在这个查询上摸不着头脑了几个小时，但没有取得任何进展。

我使用的 View 中有多个预订。所有这些预订都有 payment_method，例如 Paypal 或信用卡。

Customer Name | Price | payment_method
John Doe      |  20   |  creditcard
Susan Soe     |  10   |  paypal

通过我的 sql 查询，我试图获取所有 payment_methods 的总和。不幸的是，本月未使用的 payment_methods 没有出现。

所以我创建了一个名为 PaymentMethods 的附加表，其中包含“方法”列。

Methods
creditcard
premium
sofort
bank
paypal

然后我尝试使用 LEFT JOIN 来获得如下结果:

payment_method  | TotalQuantity (->sum(price))
creditcard      | 20
premium         | 0
sofort          | 0
bank            | 0
paypal          | 10

但相反，我只返回那些已经在当月使用了 payment_method 的款项。

payment_method  | TotalQuantity (-> sum(price))
creditcard      | 20
paypal          | 10

这是我的声明。对于我在这里做错的一些意见，我真的很感激。

SELECT payment_method, SUM(IFNULL(price,0)) AS TotalQuantity 
FROM PaymentMethods 
LEFT JOIN PaymentMethods
ON SlotBookingsFullView.payment_method=PaymentMethods.Methods
WHERE (booking_date BETWEEN '2018-07-01 00:00:00' AND '2018-07-30 23:59:59') 

* 编辑 *m0lochwalker 指出我应该使用 GROUP BY 子句...我有它，但它是在复制和粘贴操作期间得到的。这里是。

GROUP BY PaymentMethods.Methods ORDER BY PaymentMethods.Methods DESC

Answer 1

当您外连接记录时，它们的值为空:

PaymentMethods.Methods  | B.payment_method  | B.booking_date   | B.Quantity------------------------+-------------------+------------------+-----------creditcard              | creditcard        | 2018-07-01       | 2creditcard              | creditcard        | 2018-07-05       | 1creditcard              | creditcard        | 2018-04-21       | 4premium                 | NULL              | NULL             | NULL...

So with

WHERE (SlotBookingsFullView.booking_date BETWEEN '2018-07-01 00:00:00'
                                             AND '2018-07-30 23:59:59')

您要排除所有外部连接的行，因为它们的 booking_date 不在所需的日期范围内，但显然为空。这使您的加入仅仅是内部加入。将外部连接的条件放在它们的 ON 子句中:

SELECT pm.Methods, COALESCE(SUM(b.Price), 0) AS TotalQuantity 
FROM PaymentMethods pm
LEFT JOIN SlotBookingsFullView b
  ON  b.payment_method = pm.Methods
  AND b.booking_date >= date '2018-07-01'
  AND b.booking_date <  date '2018-07-31' 
GROUP BY pm.Methods
ORDER BY pm.Methods DESC;

(如您所见，COALESCE 或 IFNULL 属于 SUM 之外而不是其中，因为如果 sum 为空，你想用零替换它。)