如果没有为特定情况返回结果，则使用 count(*) 显示零 [英] Display zero by using count(*) if no result returned for a particular case

问题描述

我有一个这样的查询，它返回 city 中每个案例的行数.

I have a query like this that returns number of rows for each case in city .

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 返回的行，则最终结果中将省略该城市

So if there is no rows returned from a city , that city is omitted in final result

我想要结果

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，如下所示:

I would insert the cities into a temporary table, then do a LEFT JOIN with the grouping query as follows:

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 作为普通表是有意义的，这样您就无需在每次查询运行时都创建它.

BTW, it would make sense to have #cities as a normal table so that you don't need to create it everytime the query runs.

这篇关于如果没有为特定情况返回结果，则使用 count(*) 显示零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！