SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `dictionary_item`
-- ----------------------------
DROP TABLE IF EXISTS `dictionary_item`;
CREATE TABLE `dictionary_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(11) DEFAULT NULL COMMENT '主标题ID',
`name` varchar(100) NOT NULL COMMENT '值',
`parent_id` int(11) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL COMMENT '说明',
`the_sort` int(11) DEFAULT '1000' COMMENT '排序',
PRIMARY KEY (`id`),
KEY `ix_category_id` (`topic_id`) USING BTREE,
CONSTRAINT `dictionary_item_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `dictionary_topic` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=607 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of dictionary_item
-- ----------------------------
INSERT INTO `dictionary_item` VALUES ('490', '11', '医疗单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('491', '11', '市级', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('492', '11', '镇(区)级', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('493', '11', '其它', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('494', '11', 'test1', '491', null, '1000');
INSERT INTO `dictionary_item` VALUES ('500', '11', 'test2', '492', null, '1000');
INSERT INTO `dictionary_item` VALUES ('508', '11', 'test3', '493', null, '1000');
INSERT INTO `dictionary_item` VALUES ('510', '11', '行政事业单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('511', '11', '行政机关', '510', null, '1000');
INSERT INTO `dictionary_item` VALUES ('512', '11', '事业单位', '510', null, '1000');
INSERT INTO `dictionary_item` VALUES ('513', '11', 'test4', '511', null, '1000');
INSERT INTO `dictionary_item` VALUES ('524', '11', 'test5', '512', null, '1000');
INSERT INTO `dictionary_item` VALUES ('544', '11', '企业单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('545', '11', 'test6', '544', null, '1000');mysql实现如下查询结果:
name1 name2 name3
医疗单位 市级 test1
医疗单位 镇(区)级 test2
医疗单位 其它 test3
行政事业单位 行政机关 test4
行政事业单位 事业单位 test5
企业单位 test6 null或空
-- ----------------------------
-- Table structure for `dictionary_item`
-- ----------------------------
DROP TABLE IF EXISTS `dictionary_item`;
CREATE TABLE `dictionary_item` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`topic_id` int(11) DEFAULT NULL COMMENT '主标题ID',
`name` varchar(100) NOT NULL COMMENT '值',
`parent_id` int(11) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL COMMENT '说明',
`the_sort` int(11) DEFAULT '1000' COMMENT '排序',
PRIMARY KEY (`id`),
KEY `ix_category_id` (`topic_id`) USING BTREE,
CONSTRAINT `dictionary_item_ibfk_1` FOREIGN KEY (`topic_id`) REFERENCES `dictionary_topic` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=607 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of dictionary_item
-- ----------------------------
INSERT INTO `dictionary_item` VALUES ('490', '11', '医疗单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('491', '11', '市级', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('492', '11', '镇(区)级', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('493', '11', '其它', '490', null, '1000');
INSERT INTO `dictionary_item` VALUES ('494', '11', 'test1', '491', null, '1000');
INSERT INTO `dictionary_item` VALUES ('500', '11', 'test2', '492', null, '1000');
INSERT INTO `dictionary_item` VALUES ('508', '11', 'test3', '493', null, '1000');
INSERT INTO `dictionary_item` VALUES ('510', '11', '行政事业单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('511', '11', '行政机关', '510', null, '1000');
INSERT INTO `dictionary_item` VALUES ('512', '11', '事业单位', '510', null, '1000');
INSERT INTO `dictionary_item` VALUES ('513', '11', 'test4', '511', null, '1000');
INSERT INTO `dictionary_item` VALUES ('524', '11', 'test5', '512', null, '1000');
INSERT INTO `dictionary_item` VALUES ('544', '11', '企业单位', null, null, '1000');
INSERT INTO `dictionary_item` VALUES ('545', '11', 'test6', '544', null, '1000');mysql实现如下查询结果:
name1 name2 name3
医疗单位 市级 test1
医疗单位 镇(区)级 test2
医疗单位 其它 test3
行政事业单位 行政机关 test4
行政事业单位 事业单位 test5
企业单位 test6 null或空
http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/02/4142971.aspx
from `dictionary_item` a
left join `dictionary_item` b on a.`id` =b.`parent_id`
left join `dictionary_item` c on b.`id` =c.`parent_id`
left join `dictionary_item` d on c.`id` =d.`parent_id`
...