sql - 在 SQL Server 中显示 COUNT = 0 的行

Question

以下查询仅返回有订单的地区的地区名称。

   SELECT r.RegionName,
          COUNT (DISTINCT o.uid)
     FROM Orders AS o
LEFT JOIN Customers AS c ON o.CustomerID = c.uid
LEFT JOIN Regions AS r ON c.Region = r.uid
    WHERE (r.RegionName NOT LIKE 'NULL') 
      AND (r.RegionName <> '') 
      AND (r.RegionName NOT LIKE 'Region 1') 
      AND (o.DateOrdered LIKE '7%2011%')
 GROUP BY r.RegionName
 ORDER BY r.RegionName

如何修改它，以便即使“COUNT”为“0”也能显示所有区域名称？

Answer 1

您需要将 JOIN 更改为 Regions 以成为 RIGHT JOIN 或使 Regions 成为 FROM 表，然后 JOIN 从那里到其他表。

我更喜欢第二种方法，因为它对我来说更直观。您关心这里的区域并试图获取有关区域的信息，因此该信息应该在 FROM (IMO) 中:

SELECT
    R.RegionName,
    COUNT(O.uid)
FROM
    Regions R
LEFT OUTER JOIN Customers C ON C.Region = R.uid  -- I really don't like this naming convention
LEFT OUTER JOIN Orders O ON
    O.CustomerID = C.uid AND
    O.DateOrdered LIKE '7%2011%'  -- Is your date really stored as a string? Ugh!
WHERE
    R.RegionName <> 'NULL' AND   -- This is VERY bad...
    R.RegionName <> '' AND
    R.RegionName <> 'Region 1'
GROUP BY
    R.RegionName
ORDER BY
    R.RegionName