同一张地区表中根据汉字查询地区的代码

表结构

CREATE TABLE `t_sys_city` (
  `id` varchar(64) NOT NULL COMMENT '标示',
  `city_name` varchar(64) DEFAULT NULL COMMENT '城市名称',
  `parent_id` varchar(64) DEFAULT NULL COMMENT '父id',
  `city_level` int(11) DEFAULT NULL COMMENT '级数',
  `initial` varchar(16) DEFAULT NULL COMMENT '首字母',
  `alphabet` varchar(64) DEFAULT NULL COMMENT '全拼',
  `area_code` varchar(16) DEFAULT NULL COMMENT '区号',
  `state` char(1) DEFAULT '0' COMMENT '表状态（0：正常；1：作废）',
  `create_name` varchar(32) DEFAULT NULL COMMENT '创建人',
  `create_time` varchar(32) DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`) USING BTREE,
  KEY `state` (`state`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

 

-- 右外连接
SELECT
  p.id provinceId,
  c.id cityId,
  a.id areaId
FROM
  t_sys_city p
  RIGHT JOIN t_sys_city c
    ON p.id = c.parent_id
  RIGHT JOIN t_sys_city a
    ON c.id = a.parent_id
WHERE p.city_name LIKE '山东%'
  AND c.city_name LIKE '济南%'
  AND a.city_name LIKE '历下%';

-- 内连接
SELECT
  p.id provinceId,
  c.id cityId,
  a.id areaId
FROM
  t_sys_city p,
  t_sys_city c,
  t_sys_city a
WHERE p.id = c.parent_id
  AND c.id = a.parent_id
  AND p.city_name LIKE '山东%'
  AND c.city_name LIKE '聊城%'
  AND a.city_name LIKE '冠县%';