数据库版本mysql5。表名:user_info。每条记录都有loginName(子节点)和recommendName(父节点),通过如下语句,可实现查出某个loginName(子节点)的所有recommendName(父节点),注意:若传参为varchar,必须指明长度。(在csdn上查阅了许多前辈的文章)
DELIMITER $$
CREATE FUNCTION getfatherCategory4 (rName VARCHAR(255)) RETURNS CHAR(255) READS SQL DATABEGIN
DECLARE fName VARCHAR(255) DEFAULT rName;
DECLARE str CHAR(255) DEFAULT rName;
WHILE rName IS NOT NULL DO
SET fName=
(
SELECT recommondName FROM user_info WHERE loginName=rName
);
IF fName != '-1' THEN
SET str=CONCAT(str,',',fName);
SET rName=fName;
ELSE SET rName=NULL;
END IF;
END WHILE;
RETURN str;
END $$
下面是sql查询语句:
SELECT id,loginName,recommondName,flag FROM user_info WHERE FIND_IN_SET(loginName,getfatherCategory4('wsgws123')) ORDER BY recommondName;