我有这样的查询,它返回城市中每个案例的行数.
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作为普通表是有意义的,这样你就不需要在每次运行查询时都创建它.
