在SQL Server中显示计数为0的行

The following query only returns Region Names for regions where there have been orders.

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

   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

How can I modify it so that all region names show up even when the "COUNT" is "0"?

如何修改它，使所有区域名称都显示出来，即使“COUNT”是“0”?

1 个解决方案

#1

You need to either change your JOIN to Regions to be a RIGHT JOIN or make Regions the FROM table and then JOIN to the other tables from there.

您需要将连接到区域更改为正确的连接，或者将区域设置为FROM表，然后从该表连接到其他表。

I prefer the second method, since it seems more intuitive to me. You care about Regions here and you're trying to get information about Regions, so that should be in the FROM (IMO):

我更喜欢第二种方法，因为它对我来说更直观。你关心的是这里的区域，你试图获得关于区域的信息，所以应该在(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

#1