创建表SQL
DROP TABLE IF EXISTS `tab_category`;
CREATE TABLE `tab_category` (
`id` int(11) unsigned NOT NULL auto_increment,
`catename` varchar(30) default '' COMMENT '分配名称',
`sort` int(11) unsigned default '0' COMMENT '排序',
`path` varchar(50) default '0',
`pid` int(11) unsigned default '0' COMMENT '父级id',
PRIMARY KEY (`id`),
KEY `catename` (`catename`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
测试数据
INSERT INTO `tab_category` VALUES ('1', 'A', '0', '0', '0');
INSERT INTO `tab_category` VALUES ('2', 'B', '0', '0', '0');
INSERT INTO `tab_category` VALUES ('3', 'A-1', '0', '0-1', '1');
INSERT INTO `tab_category` VALUES ('4', 'A-2', '0', '0-1', '1');
INSERT INTO `tab_category` VALUES ('5', 'B-1', '0', '0-2', '2');
INSERT INTO `tab_category` VALUES ('6', 'B-2', '0', '0-2', '2');
INSERT INTO `tab_category` VALUES ('7', 'B-1-1', '0', '0-2-5', '5');
INSERT INTO `tab_category` VALUES ('8', 'B-1-2', '0', '0-2-5', '5');
组装SQL查询
SELECT id,catename,path,concat(path,'-',id) as fullpath
FROM tab_category ORDER BY fullpath asc;
查询结果
无限极分类-全路径方式.png
