资讯详情

MySql 递归应用

MySql版本8.0.12

    • 全国地区编码表
    • 需求1:找出安平县及其上级区域的编码和名称
      • 方法1：使用`WITH RECURSIVE`递归（版本8.0以上支持）
      • 方法二：使用纯SQL语句
      • 方法3：新字段`area_parent_path`(路径枚举)
    • 需求2:查询襄阳所有下级地区
      • 方法1：使用`WITH RECURSIVE`递归
      • 方法二：使用纯SQL语句
      • 方法3:使用内置函数`FIND_IN_SET`
    • 总结

全国地区编码表

#创建表 CREATE TABLE `region` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `area_code` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,   `area_parent_code` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,   `area_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,   `area_type` tinyint(4) unsigned DEFAULT NULL COMMENT 区域类型(1国家2省3市4区5街),   PRIMARY KEY (`id`) USING BTREE,   UNIQUE KEY `idx_area_code` (`area_code`) USING BTREE,   KEY `idx_area_name` (`area_name`) USING BTREE,
  KEY `idx_area_parent_code` (`area_parent_code`)
) ENGINE=InnoDB AUTO_INCREMENT=65536 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

#插入数据（部分）
INSERT INTO `region` VALUES (2, '110000', '1', '1', '北京', 2);
INSERT INTO `region` VALUES (3, '110100', '110000', '110000,1', '北京市', 3);
INSERT INTO `region` VALUES (4, '110101', '110100', '110100,110000,1', '东城区', 4);
INSERT INTO `region` VALUES (5, '110102', '110100', '110100,110000,1', '西城区', 4);
INSERT INTO `region` VALUES (6, '110105', '110100', '110100,110000,1', '朝阳区', 4);
INSERT INTO `region` VALUES (7, '110106', '110100', '110100,110000,1', '丰台区', 4);
INSERT INTO `region` VALUES (8, '110107', '110100', '110100,110000,1', '石景山区', 4);
INSERT INTO `region` VALUES (9, '110108', '110100', '110100,110000,1', '海淀区', 4);
INSERT INTO `region` VALUES (10, '110109', '110100', '110100,110000,1', '门头沟区', 4);
INSERT INTO `region` VALUES (11, '110111', '110100', '110100,110000,1', '房山区', 4);
INSERT INTO `region` VALUES (12, '110112', '110100', '110100,110000,1', '通州区', 4);
INSERT INTO `region` VALUES (13, '110113', '110100', '110100,110000,1', '顺义区', 4);
INSERT INTO `region` VALUES (14, '110114', '110100', '110100,110000,1', '昌平区', 4);
INSERT INTO `region` VALUES (15, '110115', '110100', '110100,110000,1', '大兴区', 4);
INSERT INTO `region` VALUES (16, '110116', '110100', '110100,110000,1', '怀柔区', 4);
INSERT INTO `region` VALUES (17, '110117', '110100', '110100,110000,1', '平谷区', 4);
INSERT INTO `region` VALUES (18, '110118', '110100', '110100,110000,1', '密云区', 4);
INSERT INTO `region` VALUES (19, '110119', '110100', '110100,110000,1', '延庆区', 4);
INSERT INTO `region` VALUES (3660, '110101001', '110101', '110101,110100,110000,1', '东华门街道', 5);
INSERT INTO `region` VALUES (3661, '110101002', '110101', '110101,110100,110000,1', '景山街道', 5);
INSERT INTO `region` VALUES (3662, '110101003', '110101', '110101,110100,110000,1', '交道口街道', 5);
INSERT INTO `region` VALUES (3663, '110101004', '110101', '110101,110100,110000,1', '安定门街道', 5);
INSERT INTO `region` VALUES (3664, '110101005', '110101', '110101,110100,110000,1', '北新桥街道', 5);
INSERT INTO `region` VALUES (3665, '110101006', '110101', '110101,110100,110000,1', '东四街道', 5);
INSERT INTO `region` VALUES (3666, '110101007', '110101', '110101,110100,110000,1', '朝阳门街道', 5);
INSERT INTO `region` VALUES (3667, '110101008', '110101', '110101,110100,110000,1', '建国门街道', 5);
INSERT INTO `region` VALUES (3668, '110101009', '110101', '110101,110100,110000,1', '东直门街道', 5);
INSERT INTO `region` VALUES (3669, '110101010', '110101', '110101,110100,110000,1', '和平里街道', 5);
INSERT INTO `region` VALUES (3670, '110101011', '110101', '110101,110100,110000,1', '前门街道', 5);
INSERT INTO `region` VALUES (3671, '110101012', '110101', '110101,110100,110000,1', '崇文门外街道', 5);
INSERT INTO `region` VALUES (3672, '110101013', '110101', '110101,110100,110000,1', '东花市街道', 5);
INSERT INTO `region` VALUES (3673, '110101014', '110101', '110101,110100,110000,1', '龙潭街道', 5);
INSERT INTO `region` VALUES (3674, '110101015', '110101', '110101,110100,110000,1', '体育馆路街道', 5);
INSERT INTO `region` VALUES (3675, '110101016', '110101', '110101,110100,110000,1', '天坛街道', 5);
INSERT INTO `region` VALUES (3676, '110101017', '110101', '110101,110100,110000,1', '永定门外街道', 5);
INSERT INTO `region` VALUES (3677, '110102001', '110102', '110102,110100,110000,1', '西长安街街道', 5);
INSERT INTO `region` VALUES (3678, '110102003', '110102', '110102,110100,110000,1', '新街口街道', 5);
INSERT INTO `region` VALUES (3679, '110102007', '110102', '110102,110100,110000,1', '月坛街道', 5);
INSERT INTO `region` VALUES (3680, '110102009', '110102', '110102,110100,110000,1', '展览路街道', 5);
INSERT INTO `region` VALUES (3681, '110102010', '110102', '110102,110100,110000,1', '德胜街道', 5);
INSERT INTO `region` VALUES (3682, '110102011', '110102', '110102,110100,110000,1', '金融街街道', 5);
INSERT INTO `region` VALUES (3683, '110102012', '110102', '110102,110100,110000,1', '什刹海街道', 5);
INSERT INTO `region` VALUES (3684, '110102013', '110102', '110102,110100,110000,1', '大栅栏街道', 5);
INSERT INTO `region` VALUES (3685, '110102014', '110102', '110102,110100,110000,1', '天桥街道', 5);
INSERT INTO `region` VALUES (3686, '110102015', '110102', '110102,110100,110000,1', '椿树街道', 5);
INSERT INTO `region` VALUES (3687, '110102016', '110102', '110102,110100,110000,1', '陶然亭街道', 5);
INSERT INTO `region` VALUES (3688, '110102017', '110102', '110102,110100,110000,1', '广安门内街道', 5);
INSERT INTO `region` VALUES (3689, '110102018', '110102', '110102,110100,110000,1', '牛街街道', 5);
INSERT INTO `region` VALUES (3690, '110102019', '110102', '110102,110100,110000,1', '白纸坊街道', 5);
INSERT INTO `region` VALUES (3691, '110102020', '110102', '110102,110100,110000,1', '广安门外街道', 5);
INSERT INTO `region` VALUES (3692, '110105001', '110105', '110105,110100,110000,1', '建外街道', 5);
INSERT INTO `region` VALUES (3693, '110105002', '110105', '110105,110100,110000,1', '朝外街道', 5);
INSERT INTO `region` VALUES (3694, '110105003', '110105', '110105,110100,110000,1', '呼家楼街道', 5);
INSERT INTO `region` VALUES (3695, '110105004', '110105', '110105,110100,110000,1', '三里屯街道', 5);
INSERT INTO `region` VALUES (3696, '110105005', '110105', '110105,110100,110000,1', '左家庄街道', 5);
INSERT INTO `region` VALUES (3697, '110105006', '110105', '110105,110100,110000,1', '香河园街道', 5);
INSERT INTO `region` VALUES (3698, '110105007', '110105', '110105,110100,110000,1', '和平街街道', 5);
INSERT INTO `region` VALUES (3699, '110105008', '110105', '110105,110100,110000,1', '安贞街道', 5);
INSERT INTO `region` VALUES (3700, '110105009', '110105', '110105,110100,110000,1', '亚运村街道', 5);
INSERT INTO `region` VALUES (3701, '110105010', '110105', '110105,110100,110000,1', '小关街道', 5);
INSERT INTO `region` VALUES (3702, '110105011', '110105', '110105,110100,110000,1', '酒仙桥街道', 5);
INSERT INTO `region` VALUES (3703, '110105012', '110105', '110105,110100,110000,1', '麦子店街道', 5);
INSERT INTO `region` VALUES (3704, '110105013', '110105', '110105,110100,110000,1', '团结湖街道', 5);
INSERT INTO `region` VALUES (3705, '110105014', '110105', '110105,110100,110000,1', '六里屯街道', 5);
INSERT INTO `region` VALUES (3706, '110105015', '110105', '110105,110100,110000,1', '八里庄街道', 5);
INSERT INTO `region` VALUES (3707, '110105016', '110105', '110105,110100,110000,1', '双井街道', 5);
INSERT INTO `region` VALUES (3708, '110105017', '110105', '110105,110100,110000,1', '劲松街道', 5);
INSERT INTO `region` VALUES (3709, '110105018', '110105', '110105,110100,110000,1', '潘家园街道', 5);
INSERT INTO `region` VALUES (3710, '110105019', '110105', '110105,110100,110000,1', '垡头街道', 5);
INSERT INTO `region` VALUES (3711, '110105021', '110105', '110105,110100,110000,1', '南磨房镇', 5);
INSERT  

        标签： 温泉深水井液位传感器深井液位计
 
锐单商城拥有海量元器件数据手册、
          IC替代型号，打造
          电子元器件IC百科大全！

锐单商城 - 一站式电子元器件采购平台