select 城市 ,
sum(case when 货类 = '20' when 1 else 0 end) 货类20,
sum(case when 货类 = '40' when 1 else 0 end) 货类40,
sum(case when 货类 = 'HQ' when 1 else 0 end) 货类HQ
from city
group by 城市
order by 城市;
sum(case when 货类 = '20' when 1 else 0 end) 货类20,
sum(case when 货类 = '40' when 1 else 0 end) 货类40,
sum(case when 货类 = 'HQ' when 1 else 0 end) 货类HQ
from city
group by 城市
order by 城市;
sum(case when 货类 = '20' then 1 else 0 end) 货类20,
sum(case when 货类 = '40' then 1 else 0 end) 货类40,
sum(case when 货类 = 'HQ' then 1 else 0 end) 货类HQ
from city
group by 城市
order by 城市;
sum(case when 货类 = '20' then 1 else 0 end) 货类20,
sum(case when 货类 = '40' then 1 else 0 end) 货类40,
sum(case when 货类 = 'HQ' then 1 else 0 end) 货类HQ
from city
group by 城市
order by 城市;
第一句语句中的每行的第二个when应该改成then
(select count(城市) where 货类 = '40') 货类20,
(select count(城市) where 货类 = '20') 货类40,
(select count(城市) where 货类 = ''HQ'') 货类HQ
from city
group by 城市
order by 城市
(select count(城市) from city where 货类 = '40') 货类20,
(select count(城市) from city where 货类 = '20') 货类40,
(select count(城市) from city where 货类 = ''HQ'') 货类HQ
from city
group by 城市
order by 城市
(select count(城市) from city c1 where 货类 = '40' and c1.城市=c.城市) 货类20,
(select count(城市) from city c2 where 货类 = '20' and c2.城市=c.城市) 货类40,
(select count(城市) from city c3 where 货类 = ''HQ'' and c3.城市=c.城市) 货类HQ
from city c