如果用 group by 子句找不到记录，MySQL 返回 0 作为值

Question

我想按月获取所有客户订单，我成功获得了查询，但如果任何客户在任何月份没有订单，我需要 0 或 null。

例如:

+--------------+----------------+-------------+
|     date     |    customer    |    order    |
+--------------+----------------+-------------+
| january 2016 | abc            |    2345     |
| Febuary 2016 | abc            |    2234     |
| january 2016 | xyz            |    2345     |
| febuary 2016 | xyz            |   0/null    |
+--------------+----------------+-------------+

但我的 sql 查询返回类似这样的内容

+--------------+----------------+-------------+
|     date     |    customer    |    order    |
+--------------+----------------+-------------+
| january 2016 | abc            |    2345     |
| Febuary 2016 | abc            |    2234     |
| january 2016 | xyz            |    2345     |
+--------------+----------------+-------------+

我的查询

SELECT 
CONCAT(so.month, ' ', so.year) months,
  c.customer_name,
  so.qty
FROM
  customer c
JOIN
  (
  SELECT
      o.customer_id,
      MONTHNAME(o.date) MONTH,
      YEAR(o.date) YEAR,
      SUM(o.quantity) qty
  FROM
    sale_order o
  WHERE
    o.order_type = 'kg'
      AND
    o.date BETWEEN '2015-01-01' AND '2016-01-01'
  GROUP BY YEAR(o.date), MONTHNAME(o.date), o.customer_id
) so 
on so.customer_id = c.customer_id

Answer 1

您可以选择月份和年份的所有不同值，然后左连接到您的其余结果，如下所示:

SELECT 
      CONCAT(t.month, ' ', t.year) months,
      c.customer_name,
      so.qty
FROM (select distinct MONTHNAME(date) as month,year(date) as year from sale_order
      where date BETWEEN '2015-01-01' AND '2016-01-01') t
INNER JOIN customer c
 ON(1=1)
LEFT OUTER JOIN
  (
  SELECT
      o.customer_id,
      MONTHNAME(o.date) MONTH,
      YEAR(o.date) YEAR,
      SUM(o.quantity) qty
  FROM
    sale_order o
  WHERE
    o.order_type = 'kg'
      AND
    o.date BETWEEN '2015-01-01' AND '2016-01-01'
  GROUP BY YEAR(o.date), MONTHNAME(o.date), o.customer_id
) so 
 on (so.customer_id = c.customer_id and
    so.year = t.year and
    so.month = t.month)