select
c.cn as '省',c.cn_city as '市',
SUM(CASE t.placeId when 1 then t.num else 0 end ) as '餐饮' ,
SUM(CASE t.placeId when 2 then t.num else 0 end ) as '咖啡厅',
SUM(CASE t.placeId when 3 then t.num else 0 end ) as '影院',
SUM(CASE t.placeId when 4 then t.num else 0 end ) as '酒吧',
SUM(CASE t.placeId when 5 then t.num else 0 end ) as '酒店',
SUM(CASE t.placeId when 6 then t.num else 0 end ) as '娱乐',
SUM(CASE t.placeId when 7 then t.num else 0 end ) as '台球厅',
SUM(CASE t.placeId when 8 then t.num else 0 end ) as '商场',
SUM(CASE t.placeId when 9 then t.num else 0 end ) as '高尔夫',
SUM(CASE t.placeId when 10 then t.num else 0 end ) as '4S店' ,
SUM(CASE t.placeId when 11 then t.num else 0 end ) as '美容美发',
SUM(CASE t.placeId when 12 then t.num else 0 end ) as '网吧' ,
SUM(CASE t.placeId when 13 then t.num else 0 end ) as '机场' ,
SUM(CASE t.placeId when 14 then t.num else 0 end ) as '高铁',
SUM(CASE t.placeId when 15 then t.num else 0 end ) as '港口码头',
SUM(CASE t.placeId when 16 then t.num else 0 end ) as '长途客运',
SUM(CASE t.placeId when 17 then t.num else 0 end ) as '公交车',
SUM(CASE t.placeId when 18 then t.num else 0 end ) as '出租车',
SUM(CASE t.placeId when 19 then t.num else 0 end ) as '地铁',
SUM(CASE t.placeId when 20 then t.num else 0 end ) as '停车场',
SUM(CASE t.placeId when 21 then t.num else 0 end ) as '工厂园区',
SUM(CASE t.placeId when 22 then t.num else 0 end ) as '社区',
SUM(CASE t.placeId when 23 then t.num else 0 end ) as '学校',
SUM(CASE t.placeId when 24 then t.num else 0 end ) as '写字楼',
SUM(CASE t.placeId when 25 then t.num else 0 end ) as '行政中心',
SUM(CASE t.placeId when 26 then t.num else 0 end ) as '医院',
SUM(CASE t.placeId when 27 then t.num else 0 end ) as '景区',
SUM(CASE t.placeId when 28 then t.num else 0 end ) as '银行',
SUM(CASE t.placeId when 29 then t.num else 0 end ) as '通信运营商',
SUM(CASE t.placeId when 30 then t.num else 0 end ) as '智慧城市',
SUM(CASE t.placeId when 31 then t.num else 0 end ) as '其它'
from(
select a.location location ,b.class_id placeId,sum(a.num) num from
(
select location,place_id,count(1) num from t_wifi GROUP BY location,place_id union all select location,place_id,SUM(num) num from t_wifi_sum GROUP BY location,place_id
) a,t_place_class b where a.place_id = b.place_id GROUP BY a.location,b.class_id
)t ,t_location c where t.location = c.location GROUP BY c.cn,c.cn_city
order by field(c.cn,'北京','上海','天津','重庆','西藏','浙江','江苏','安徽','山东','山西','陕西','四川','云南','新疆','福建','甘肃','广东','广西','贵州','河北','河南','湖北','湖南','吉林','江西','辽宁','内蒙古','宁夏','青海','黑龙江','海南','中国其他'),
field(c.cn_city,'北京','上海','天津','重庆','拉萨','杭州','南京','合肥','济南','太原','西安','成都','昆明','乌鲁木齐','福州','兰州','广州','桂林','贵阳','石家庄','郑州','武汉','长沙','吉林','南昌','沈阳','呼和浩特','银川','西宁','哈尔滨','海口');