問(wèn)題描述

以下查詢僅返回有訂單的地區(qū)的地區(qū)名稱.

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"也能顯示所有區(qū)域名稱?

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.

我更喜歡第二種方法，因?yàn)樗鼘?duì)我來(lái)說(shuō)似乎更直觀.您關(guān)心這里的 Regions 并且您正在嘗試獲取有關(guān) Regions 的信息，因此應(yīng)該在 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

這篇關(guān)于在 SQL Server 中顯示 COUNT = 0 的行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！