当没有结果时，mysql 在 SUM 上返回默认值 0

Question

当我添加 where 子句时，我陷入了查询困境。正如预期的那样，我没有得到任何结果，因此 SUM 没有任何可处理的内容。当没有行与 where 语句匹配时，我尝试为 countSend 和 countPending 返回默认值 0。

SELECT
  SUM(CASE WHEN orders.status = "send" THEN 1 ELSE 0 END) countSend,
  SUM(CASE WHEN orders.status = "pending" THEN 1 ELSE 0 END) countPending
FROM orders
  LEFT OUTER JOIN order_posts AS op ON op.order_id = orders.id
  LEFT OUTER JOIN posts AS p ON p.id = op.post_id
WHERE (orders.id LIKE "%Shop 3%") OR (p.title LIKE "%Shop 3%")
  GROUP BY orders.id
  LIMIT 1

Answer 1

我不太确定您想要完成什么，但是GROUP BY可能不是必需的。以下将始终返回一行:

SELECT SUM(o.status = 'send') as countSend,
       SUM(o.status = 'pending') as countPending
FROM orders o LEFT OUTER JOIN
     order_posts op
     ON op.order_id = o.id LEFT OUTER JOIN
     posts p
     ON p.id = op.post_id
WHERE (o.id LIKE '%Shop 3%') OR (p.title LIKE '%Shop 3%');

如果 WHERE 子句过滤掉所有内容，您仍然会得到一行，其中包含 NULL 值。使用 COALESCE() 来返回 0:

SELECT COALESCE(SUM(o.status = 'send'), 0) as countSend,
       COALESCE(SUM(o.status = 'pending'), 0) as countPending