MySQL - 按空值分组

Question

我有以下 SQL 查询:

SELECT date_format(sfo.shipping_arrival_date,"%m.%Y") AS Month,
       date_format(sfo.shipping_arrival_date,"%v") AS Week,
       date_format(sfo.shipping_arrival_date,"%d.%m.%Y") AS Shipping Day,
       CASE
           WHEN sfo.coupon_code IS NOT NULL THEN sfo.coupon_code
           ELSE 'no Code'
       END as Coupon,
       sfo.coupon_rule_name,
       sfo.grand_total AS Endsumme,
       sfo.base_subtotal + sfo.shipping_incl_tax AS Sum_Incl_Shipping,
       sfo.discount_amount,
       count(sfo.entity_id) AS uses
FROM sales_flat_order AS sfo
WHERE sfo.status <> 'canceled'
GROUP BY Coupon
ORDER BY sfo.shipping_arrival_date DESC

在 sfo.coupon_code 中存在值“NULL”，但查询不会在结果中将它们列为“无代码”。我想要一组优惠券代码和相关值。

有人知道为什么我在结果中找不到值“no Code”吗？

谢谢。

Answer 1

我的预感是对于 sfo.status = NULL 的记录，您只有 coupon_code 的 NULL 值，这些值在您的结果集。

尝试以下查询:

SELECT date_format(sfo.shipping_arrival_date,"%m.%Y") AS Month,
       date_format(sfo.shipping_arrival_date,"%v") AS Week,
       date_format(sfo.shipping_arrival_date,"%d.%m.%Y") AS Shipping Day,
       CASE
           WHEN sfo.coupon_code IS NOT NULL THEN sfo.coupon_code
           ELSE 'no Code'
       END as Coupon,
       sfo.coupon_rule_name,
       sfo.grand_total AS Endsumme,
       sfo.base_subtotal + sfo.shipping_incl_tax AS Sum_Incl_Shipping,
       sfo.discount_amount,
       count(sfo.entity_id) AS uses
FROM sales_flat_order AS sfo
WHERE sfo.status <> 'canceled' OR sfo.status IS NULL
GROUP BY Coupon
ORDER BY sfo.shipping_arrival_date DESC