我有一个这样的查询,它返回 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 Total
MUMBAI 1
DELHI 28
CHENNAI 1
KOLKATA 35
AHMEDABAD 3
BANGALORE 0 -- if no result from bangalore display zero.
这个怎么做 ?
I tried
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
作为普通表,这样您就不需要每次运行查询时都创建它。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)