JiaHe

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `region`;
CREATE TABLE `region`  (
  `id` int(9) UNSIGNED NOT NULL COMMENT '城市编号',
  `pid` int(9) NOT NULL COMMENT '上级ID',
  `deep` int(1) NOT NULL COMMENT '层级深度',
  `name` varchar(255) NOT NULL COMMENT '简短地区名称',
  `pinyin_prefix` varchar(255) NOT NULL COMMENT '拼音前缀',
  `pinyin` varchar(255) NOT NULL COMMENT '完整拼音',
  `ext_id` bigint(13) NOT NULL COMMENT '数据源原始的编号',
  `ext_name` varchar(255) NOT NULL COMMENT '数据源原始的完整名称',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pid_idx`(`pid`) USING BTREE,
  INDEX `name_idx`(`name`) USING BTREE,
  INDEX `ext_name_idx`(`ext_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

SELECT
	t2.province_name 省, t2.city_name 市, t2.district_name 区, t2.county_name "乡/县/街道"
FROM
	(
		SELECT
			r.id province_code, r.name province_name, r1.name city_name, r2.name district_name, r3.name county_name
		FROM
			region r
			LEFT JOIN region r1 ON r.id = r1.pid AND r1.deep = 1
			LEFT JOIN region r2 ON r1.id = r2.pid AND r2.deep = 2
			LEFT JOIN region r3 ON r2.id = r3.pid AND r3.deep = 3
		WHERE
			r.deep = 0
	) t2
WHERE
	-- '亚运村街道' 替换为需要补全或检索的地址关键词
	(
		INSTR ( '亚运村街道', t2.province_name )
		OR INSTR ( '亚运村街道', t2.city_name )
		OR INSTR ('亚运村街道', t2.district_name)
		OR  INSTR ('亚运村街道', t2.county_name)
	)
	AND INSTR ( '亚运村街道', t2.county_name)