mysql 查询每个省有多少个城市 怎么查有 两张表 城市表 和省份表 pid链接 sql语句怎么写

SELECT COUNT(0),PROVINCE_NAME FROM (SELECT B.CITY_NAME ,A.PROVINCE_NAME FROM PROVINCE_TABLE A,CITY_TABLE B WHERE A.PID=B.PID) GROUP BY PROVINCE_NAME

select c1.provinceid,p1.province,count(1) pronums from city c1 inner join province p1 on c1.provinceid=p1.id

group by c1.provinceid,p1.province

---