通过一张表查询完整的省市区信息
表结构
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`pid` int(11) UNSIGNED NOT NULL,
`name` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`visible` tinyint(4) UNSIGNED NOT NULL,
`displayorder` int(11) UNSIGNED NOT NULL,
`level` tinyint(3) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `parentId`(`pid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 990101 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES (110000, 0, '北京市', 2, 0, 1);
INSERT INTO `area` VALUES (110100, 110000, '北京市', 2, 0, 2);
INSERT INTO `area` VALUES (110101, 110100, '东城区', 2, 0, 3);
INSERT INTO `area` VALUES (110102, 110100, '西城区', 2, 0, 3);
INSERT INTO `area` VALUES (110103, 110100, '崇文区', 2, 0, 3);
INSERT INTO `area` VALUES (110104, 110100, '宣武区', 2, 0, 3);
INSERT INTO `area` VALUES (110105, 110100, '朝阳区', 2, 0, 3);
INSERT INTO `area` VALUES (110106, 110100, '丰台区', 2, 0, 3);
INSERT INTO `area` VALUES (110107, 110100, '石景山区', 2, 0, 3);
INSERT INTO `area` VALUES (110108, 110100, '海淀区', 2, 0, 3);
INSERT INTO `area` VALUES (110109, 110100, '门头沟区', 2, 0, 3);
sql语句
SELECT c.name province,b.name city,a.name area FROM `area` `a` INNER JOIN `area` `b` ON `a`.`pid`=`b`.`id` INNER JOIN `area` `c` ON `b`.`pid`=`c`.`id` 效果
