想以ROOT方式省市级联查询，请问如何添加这种ROOT字段

linusex007

表字段：
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `area_code` varchar(12) NOT NULL,
  `area_name` varchar(100) NOT NULL,
  `area_type` tinyint(4) DEFAULT NULL,
  `zip_code` varchar(12) DEFAULT NULL,
  `phone_code` varchar(4) DEFAULT NULL,
  `status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '状态 ，0 默认有效 -1 失效',
  `root` varchar(12) DEFAULT NULL,
  `gmt_create` datetime default now() comment '创建时间',
  `gmt_modified` datetime not null default now() comment '修改时间',
  `is_deleted` int(4) not null default 0 comment '是否已删除',

INSERT INTO `tt_areacode` VALUES ('1', '110000', '北京市', '1', null, null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('2', '110100', '市辖区', '2', null, null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('3', '110101', '东城区', '3', '100010', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('4', '110102', '西城区', '3', '100032', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('5', '110105', '朝阳区', '3', '100020', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('6', '110106', '丰台区', '3', '100071', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('7', '110107', '石景山区', '3', '100043', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('8', '110108', '海淀区', '3', '100089', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('9', '110109', '门头沟区', '3', '102300', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('10', '110111', '房山区', '3', '102488', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('11', '110112', '通州区', '3', '101100', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('12', '110113', '顺义区', '3', '101300', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('13', '110114', '昌平区', '3', '102200', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('14', '110115', '大兴区', '3', '102600', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('15', '110116', '怀柔区', '3', '101400', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('16', '110117', '平谷区', '3', '101200', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('17', '110200', '县', '2', null, null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('18', '110228', '密云县', '3', '101500', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('19', '110229', '延庆县', '3', '102100', null, '0', '2012-06-09 23:28:11', '2012-06-09 23:28:24');

烦请问下如何用sql语句将ROOT字段填上

INSERT INTO `tt_areacode` VALUES ('1', '110000', '北京市', '1', null, null, '0','', '2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('2', '110100', '市辖区', '2', null, null, '0', '','2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('3', '110101', '东城区', '3', '100010', null, '0','北京市', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('4', '110102', '西城区', '3', '100032', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('5', '110105', '朝阳区', '3', '100020', null, '0','北京市, '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('6', '110106', '丰台区', '3', '100071', null, '0', '北京市,'2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('7', '110107', '石景山区', '3', '100043', null, '0','北京市, '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('8', '110108', '海淀区', '3', '100089', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('9', '110109', '门头沟区', '3', '102300', null, '0','北京市, '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('10', '110111', '房山区', '3', '102488', null, '0','北京市, '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('11', '110112', '通州区', '3', '101100', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('12', '110113', '顺义区', '3', '101300', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('13', '110114', '昌平区', '3', '102200', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('14', '110115', '大兴区', '3', '102600', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('15', '110116', '怀柔区', '3', '101400', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('16', '110117', '平谷区', '3', '101200', null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('17', '110200', '县', '2', null, null, '0', '北京市','2012-06-09 23:28:11', '2012-06-09 23:28:12');
INSERT INTO `tt_areacode` VALUES ('18', '110228', '密云县', '3', '101500', null, '0','北京市', '2012-06-09 23:28:11', '2012-06-09 23:28:24');
INSERT INTO `tt_areacode` VALUES ('19', '110229', '延庆县', '3', '102100', null, '0','北京市', '2012-06-09 23:28:11', '2012-06-09 23:28:24');

seesea2517

总感觉还有啥问题想问lz的。不过这个肯定不是lz要的，但又不知道还想问lz什么信息：

1. update 表 set root='北京市';

复制代码

hwrd_et

我也没看明白，可能他是想要这种

UPDATE `tt_areacode` SET `root` = `area_name` WHERE `root` = ''

linusex007

回复 3# hwrd_et




   

linusex007

回复 2# seesea2517
怎么才能够在数据库sql里写 出这样的效果：
130000
130101
130102
.。。。。。。
匹配前两位13出来，后面130101、130102.。。。的root都改成130000的area_name，这样就把所有市级的ROOT显示为对应的上级省份了。
如上打个比方，数据规律自己可以看出来   

seesea2517

那就 select concat(left(area_code, 2), "000") 不就可以实现这个需求了，剩下的就是自己做一个连接、更新了。