浏览 3644 次
锁定老帖子 主题:MySQL-递归查询方法解析
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
|
|
---|---|
作者 | 正文 |
发表时间:2016-08-05
首先 表结构和数据 CREATE TABLE `class` ( `classid` int(11) NOT NULL AUTO_INCREMENT, `banji` int(11) DEFAULT NULL COMMENT '0', `nianji` varchar(255) DEFAULT NULL, PRIMARY KEY (`classid`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;-- ------------------------------ Records of class-- ----------------------------INSERT INTO `class` VALUES ('1', '0', '1');INSERT INTO `class` VALUES ('2', '1', '2');INSERT INTO `class` VALUES ('3', '1', 'f');INSERT INTO `class` VALUES ('4', '2', 'd');INSERT INTO `class` VALUES ('5', '3', 's');INSERT INTO `class` VALUES ('6', '0', 'a');INSERT INTO `class` VALUES ('7', '6', 'q');INSERT INTO `class` VALUES ('8', '7', 'h');INSERT INTO `class` VALUES ('9', '5', '额');INSERT INTO `class` VALUES ('10', '4', '121'); 方法解析1 一级写一个查询结果联合起来 SELECT * from class WHERE classid =1UNIONSELECT * FROM class where banji in(SELECT classid from class WHERE classid =1 )UNION SELECT * FROM class where banji in (SELECT classid FROM class where banji in(SELECT classid from class WHERE classid =1)) 方法解析2 使用函数方法 创建函数来获取个节点的值 CREATE FUNCTION `selectTree`(`id` int) RETURNS varchar(2000)BEGIN #Routine body goes here... DECLARE st VARCHAR(4000); DECLARE stcc VARCHAR(2000);#给st赋初值不能null,null会没有返回值 set st=''; set stcc=id;#判断stcc的是否为null WHILE stcc is not NULL DO#拼接字符串赋值给st SET st=CONCAT(st,',',stcc); SELECT GROUP_CONCAT(classid) INTO stcc FROM class where FIND_IN_SET(banji,stcc)>0;END WHILE; RETURN st;END; 调用函数查出结果 SELECT * from class where classid in (SELECT a.ban FROM class a,class b where a.banji=b.classid) and banji=0 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |