数据库版本mysql5。表名:user_info。每条记录都有loginName(子节点)和recommendName(父节点),通过如下语句,可实现查出某个loginName(子节点)的所有recommendName(父节点),注意:若传参为varchar,必须指明长度。(在csdn上查阅了许多前辈的文章)
DELIMITER $$
CREATE FUNCTION getfatherCategory4 (rName VARCHAR(255)) RETURNS CHAR(255) READS SQL DATA BEGIN 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;