无限极分类-全路径方式

创建表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;

查询结果