SQL - LEFT OUTER JOIN 和 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1281967/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - LEFT OUTER JOIN and WHERE clause
提问by user70192
I'm terrible at SQL. I do not know if what I am trying to do is possible. But, because of our data structure, I need to solve this problem this way or do a massive architectural change.
我在 SQL 方面很糟糕。我不知道我正在尝试做的事情是否可行。但是，由于我们的数据结构，我需要以这种方式解决这个问题，或者进行大规模的架构更改。
I am trying to count the number of 'Provinces' (a.k.a States) for a Country. However, there are just a few Provinces that need to be ignored from the count. Because of this, I am trying to retrieve a list of countries, with a count of the provinces in each country.
我正在尝试计算一个国家的“省”（又名州）的数量。但是，只有少数省份需要从计数中忽略。因此，我试图检索一个国家列表，其中包含每个国家/地区的省份数。
As an example, I need to query for the United States, and ignore 'Washington D.C.' from the count. The reason why is because by our requirements, Washington D.C. is not a state. Here is what I am trying at the moment (it does not work):
例如，我需要查询美国，并从计数中忽略“华盛顿特区”。原因是因为根据我们的要求，华盛顿特区不是一个州。这是我目前正在尝试的（它不起作用）：
SELECT
c.Name AS 'CountryName',
ISNULL(COUNT(p.[ID]), 0) as 'ProvinceCount'
FROM
Country c LEFT OUTER JOIN [Province] p ON p.[CountryID]=c.[ID]
WHERE
c.[ID]=@idParameter and
p.[Name] <> 'Washington D.C.'
As you can imagine, this query does not return any results when the idParameter matches that of the United States.
可以想象，当 idParameter 与美国的 idParameter 匹配时，此查询不会返回任何结果。
How do I get the correct count while figuring in exceptions? Thank you very much for your help.
在计算异常时如何获得正确的计数？非常感谢您的帮助。
回答by Kirk Broadhurst
You need a GROUP BYclause to get a proper count, and you need an outer join to display '0' values for those countries with no valid provinces.
您需要一个GROUP BY子句来获得正确的计数，并且您需要一个外连接来为那些没有有效省份的国家/地区显示“0”值。
select
c.Name as 'CountryName',
isnull(count(c.Name), 0) as 'ProvinceCount'
from
Country c
left outer join
Province p on
p.CountryID = c.[ID]
where
c.[ID] = @idParameter
and p.[Name] not in ('Washington D.C', 'Another State')
group by
c.Name
回答by JBrooks
You don't want to list out text, a typo can cause a hard to see bug. You also want to eventually make it so the user can have a page to maintain this themselves. So:
您不想列出文本，拼写错误可能会导致难以看到的错误。您还希望最终做到这一点，以便用户可以拥有一个页面来自己维护它。所以：
ALTER TABLE Province
ADD IsState bit
GO
UPDATE Province
set IsState = 1
where Name not in ('Washington D.C', 'Another State')
GO
UPDATE Province
SET IsState = 0
WHERE IsState IS NULL
GO
-- double check the data at this point by browsing it...
SELECT c.name AS 'country name',
isnull(count(1), 0) AS 'provice count'
FROM Country c
INNER JOIN Province p
ON p.CountryID = c.[ID]
WHERE c.[ID] = @idParameter
AND p.IsState = 1
GROUP BY c.name
ORDER BY 1
GO
回答by tschaible
Can you give this a try?
你可以试试这个吗？
SELECT
c.Name AS 'CountryName',
ISNULL(COUNT(*), 0) as 'ProvinceCount'
FROM
Country c LEFT OUTER JOIN Province p ON p.CountryID=c.ID and p.Name <> 'Washington D.C.'
WHERE
c.ID=@idParameter
GROUP BY c.Name
回答by Noon Silk
select
c.name as 'country name'
isnull(count(p.[ID]), 0) as 'provice count'
from
Country c
inner join
Province p on
p.CountryID = c.[ID]
where
c.[ID] = @idParameter
and
p.[Name] not in ('Washington D.C', 'Another State')
Maybe? Not tested.
也许？未测试。
-- Edit
- 编辑
Disregard this; as suggested by the poster above it needs a 'group by' to work.
无视这一点；正如上面的海报所建议的，它需要一个“group by”才能工作。
