sql - MSSQL:即使Count = 0，也显示具有大小写和计数的Select的行

我希望我能详细解释我的问题，以便你们能理解我。
香港专业教育学院创建了一个小例子。

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

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:即使Count = 0，也显示具有大小写和计数的Select的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23245869/