在 SQL Server 中显示 COUNT = 0 的行

2021-09-10 00:00:00 sql tsql sql-server

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

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

如何修改它以便即使COUNT"为0"也能显示所有区域名称?

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

推荐答案

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

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.

我更喜欢第二种方法，因为它对我来说似乎更直观.您关心这里的 Regions 并且您正在尝试获取有关 Regions 的信息，因此应该在 FROM (IMO) 中:

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):

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

相关文章