在COUNT = 0的SQL Server中显示行

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

您需要将JOINRegions更改为RegionsRIGHT 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