SQL-LEFT OUTER JOIN和WHERE子句 [英] SQL - LEFT OUTER JOIN and WHERE clause

问题描述

我在SQL方面很糟糕.我不知道我要做什么.但是，由于我们的数据结构，我需要以这种方式解决此问题或进行大规模的体系结构更改.

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.

我正在尝试计算一个国家的省"(又称州")的数量.但是，只有少数几个省需要从计数中忽略.因此，我试图检索一个国家列表，并列出每个国家的省份.

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.'

您可以想象，当idParameter匹配美国的查询时，此查询不会返回任何结果.

As you can imagine, this query does not return any results when the idParameter matches that of the United States.

在计算异常时如何获得正确的计数?非常感谢您的帮助.

How do I get the correct count while figuring in exceptions? Thank you very much for your help.

推荐答案

您需要一个GROUP BY子句以获取正确的计数，并且需要外部联接才能为那些没有有效省份的国家/地区显示"0"值.

You need a GROUP BY clause to get a proper count, and you need an outer join to display '0' values for those countries with no valid provinces.

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

这篇关于SQL-LEFT OUTER JOIN和WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！