SELECT * FROM (SELECT a.area_name,a.area_id FROM area AS a WHERE a.area_pid=104 ) d LEFT JOIN (SELECT counts ,city FROM (SELECT city,province ,COUNT(*) counts FROM useraddress GROUP BY city) b WHERE province=104) c ON d.area_id=c.city
两张表
一个area 地区表 ，一个是用户地址表 useraddress，地区表中有全国各地省市的信息，市区的父id area_pid是省的id，
用户地址表中记录省 ，市的id 号
目的是为了查询每个地区下子区的人数
例如 查询山东省 下面每个市又多少人，有些城市又值，有些城市则没有就缺省
查询后的结果如下
city user_count
济南 20
青岛 100
烟台 50
蓬莱 null
。 null
。 null
本人上面的语句可以实现，但是觉得很罗嗦，不知道怎么优化，无从下手，请高手帮忙指点
解决方案
5
SELECT a.area_name AS city,b.counts AS user_count FROM
(SELECT area_name,area_id FROM area WHERE area_pid=104 ) a
LEFT JOIN
(SELECT city,COUNT(*) counts FROM useraddress WHERE province=104 GROUP BY city) b
ON a.area_id=b.city
(SELECT area_name,area_id FROM area WHERE area_pid=104 ) a
LEFT JOIN
(SELECT city,COUNT(*) counts FROM useraddress WHERE province=104 GROUP BY city) b
ON a.area_id=b.city
15
SELECT a.area_name,a.area_id,counts,city
FROM area AS a
LEFT JOIN (SELECT city,province,COUNT(*) counts FROM useraddress WHERE province=104 GROUP BY city) c ON a.area_id=c.city
WHERE a.area_pid=104
FROM area AS a
LEFT JOIN (SELECT city,province,COUNT(*) counts FROM useraddress WHERE province=104 GROUP BY city) c ON a.area_id=c.city
WHERE a.area_pid=104
CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明求优化sql 语句 ，写的很长，能不能优化下！！
