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

添加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

最佳答案

我不太确定您要完成什么，但是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