我希望我能详细解释我的问题，以便你们能理解我。 我创建了一个小示例。

我有一个看起来像这样的表:

City       |    Name

Berlin     |    Mike
Berlin City|    Peter
Stuttgart  |    Boris

这是我的查询:

    SELECT CASE
            WHEN City like '%Berlin%' THEN 'Count Person in Berlin:'
            WHEN City like '%Stuttgart%' THEN 'Count Person in Stuttgart:'
            WHEN City like '%Dresden%' THEN  'Count Person in Dresden:'
            ELSE 'unknown'
            END AS Text,
            COUNT(Name) AS countPersons
     FROM tblTest
     GROUP BY City

这是结果:

Count Person in Berlin:     2
Count Person in Stuttgart:  1

但我想要的结果是:

Count Person in Berlin:     2
Count Person in Stuttgart:  1
Count Person in Dresden:    0

我怎样才能得到我想要的结果？希望你能帮助我。

提前致谢。

SQL Fiddle Demo

最佳答案

如果您没有包含城市列表的表，则可以使用子查询。解决这类问题的关键是left outer join:

select cities.city, count(t.city) as numpeople
from (select 'Berlin' as city union all
      select 'Stuttgart' union all
      select 'Dresden'
     ) cities left outer join
     tbltest t
     on t.city = cities.city
group by cities.city;

如果你也想拥有'unknown'，那么可以使用full outer join:

select coalesce(cities.city, 'unknown') as city, count(t.city) as numpeople
from (select 'Berlin' as city union all
      select 'Stuttgart' union all
      select 'Dresden'
     ) cities full outer join
     tbltest t
     on t.city = cities.city
group by coalesce(cities.city, 'unknown');

关于sql - MSSQL : Display Rows for a Select with Case and Count even if Count = 0，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/23245869/