sql – 如果没有为特定情况返回结果,则使用count(*)显示零

我有这样的查询,它返回城市中每个案例的行数.

select 
    case edition_id 
        when 6 then 'DELHI' 
        when 50 then 'AHMEDABAD' 
        when 4 then 'HYDERABAD' 
        when 25 then 'KOLKATA' 
        when 51 then 'BANGALORE' 
        when 5 then 'MUMBAI' 
        when 24 then 'CHENNAI' 
    end as CITY,count(*) as Total 
from #tmptab1
group by edition_id

drop table #tmptab1

结果就像是

CITY    Total
MUMBAI  1
DELHI   28
CHENNAI 1
KOLKATA 35
AHMEDABAD 3

因此,如果没有从城市返回的行,则在最终结果中省略该城市

我希望结果为

CITY    Total
MUMBAI  1
DELHI   28
CHENNAI 1
KOLKATA 35
AHMEDABAD 3
BANGALORE 0 -- if no result from bangalore display zero.

这该怎么做 ？

我试过了

case count(*)>0 then count(*) else 0 end as Total

但它不起作用

解决方法

我会将城市插入临时表,然后使用分组查询执行LEFT JOIN,如下所示：

CREATE TABLE #cities (edition_id INT,city VARCHAR(16))
INSERT INTO #cities VALUES(6,'DELHI')
INSERT INTO #cities VALUES(50,'AHMEDABAD')
INSERT INTO #cities VALUES(4,'HYDERABAD')
INSERT INTO #cities VALUES(25,'KOLKATA')
INSERT INTO #cities VALUES(51,'BANGALORE')
INSERT INTO #cities VALUES(5,'MUMBAI')
INSERT INTO #cities VALUES(24,'CHENNAI')

select 
    c.city 'City',ISNULL(t.Total,0) 'Total'
from 
    #cities c
    LEFT JOIN (
        SELECT 
            edition_id,count(*) as Total 
        #tmptab1 
        GROUP BY edition_id
    ) AS t
    ON c.edition_id = t.edition_id

drop table #tmptab1
drop table #cities

顺便说一句,将#cities作为普通表是有意义的,这样你就不需要在每次运行查询时都创建它.