先申明一下,网上下载的数据中没有包含港澳台的,可能是该平台业务没有触及到港澳台地区,港澳台是中国的固有领土神圣不可侵犯!
一、自关联查询
下载中国行政区三级sql脚本文件,导入数据库
CREATE TABLE `area` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '地区名',
`code` varchar(12) DEFAULT NULL COMMENT '地区编码',
`pid` int(11) DEFAULT NULL COMMENT '父级id',
`level` int(10) DEFAULT NULL COMMENT '级别',
`url` varchar(150) DEFAULT NULL COMMENT '链接',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3033 DEFAULT CHARSET=utf8mb4 COMMENT='行政区划';
mysql>select * from area limit 0,30;
+----+----------+--------------+------+-------+-------------------------------------------------------------------------+
| id | name | code | pid | level | url |
+----+----------+--------------+------+-------+-------------------------------------------------------------------------+
| 1 | 北京市 | 110000000000 | 0 | 1 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11.html |
| 2 | 市辖区 | 110100000000 | 1 | 2 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/1101.html |
| 3 | 东城区 | 110101000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110101.html |
| 4 | 西城区 | 110102000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110102.html |
| 5 | 朝阳区 | 110105000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110105.html |
| 6 | 丰台区 | 110106000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110106.html |
| 7 | 石景山区 | 110107000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110107.html |
| 8 | 海淀区 | 110108000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110108.html |
| 9 | 门头沟区 | 110109000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110109.html |
| 10 | 房山区 | 110111000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110111.html |
| 11 | 通州区 | 110112000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110112.html |
| 12 | 顺义区 | 110113000000 | 2 | 3 | http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2018/11/01/110113.html |
| 13 | 昌平区 | 110114000000 | 2</