sql - 未显示所有左表值的左连接

Question

LEFT JOIN 应该显示左表中的所有行，但是在运行以下查询时，我只获取计数为 1 或更多的位置值。请有人给我一些关于我可能出错的指导:

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
WHERE  places.companyId = 1
       AND stateId = 1
       AND orderstateId = 1
       AND orderName NOT LIKE 'Delivery%'
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC 

提前致谢

Answer 1

您的 WHERE条件转换 OUTER JOIN回到 INNER JOIN .

不匹配的行将有 NULL所有的orders列并被 WHERE 消除条款。试试这个。

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
       AND orders.stateId = 1
       AND orders.orderstateId = 1
       AND orders.orderName NOT LIKE 'Delivery%'
WHERE  places.companyId = 1
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC