Mysql递归查询

        前几天公司项目中有一个分销系统，需要用的递归，查出用户上家是谁，之前在学校听老师讲过，一直没使用，又重新在网上查了一些资料，算是做出来了吧。

DROP TABLE IF EXISTS `usertable`;

1、用函数实现，这个容易，没什么难度；关键就是每次查询，把查询出来的parentID重新把这个ID赋值给下次查询的userID；

DELIMITER $$ CREATE FUNCTION rec_quest(userid INT(11),socpe INT(11)) RETURNS VARCHAR(250) BEGIN #返回值； DECLARE result VARCHAR(250) DEFAULT ''; #该变量用来，查询数据; DECLARE par_id INT(11) DEFAULT -1; #该变量用来判断查询条数; DECLARE i INT(11) DEFAULT 1; WHILE par_id != 0 AND i <= socpe DO  #每查询一次，就把parentID拼接起来; SET result := CONCAT(result,',',userid); SELECT parent_id INTO par_id FROM usertable AS u WHERE u.id = userid;  #用于下一次查询； SET userid := par_id; SET i = i + 1; END WHILE; SET result := SUBSTR(result,2); RETURN result; END $$ DELIMITER ;

查询出来结果：

SELECT rec_quest(21,4); SELECT * FROM usertable WHERE FIND_IN_SET(ID,rec_quest(21,4));

2、用sql语句实现；

      ps 这个也是可以是用获取最近三条记录，用limit关键字；

SELECT  zt.*,ft.cat FROM ( #查询出递归的条数； SELECT  #这是的意思是每查一条记录，就重新为定义的变量@user_id赋值成父id，这样下一次查询就会根据父ID进行查询记录； @user_id AS user_id,(SELECT @user_id := parent_id FROM usertable WHERE ID = user_id) AS newparent_id,@cat := @cat + 1 AS cat FROM  #这里做一个全连接查询是必要的，这样可以获取出所有的记录，因为这样递归查询的记录条数； (SELECT @user_id := 21,@cat := 0) AS u1,usertable AS u2 WHERE @user_id <> 0) AS ft INNER JOIN usertable AS zt ON ft.user_id = zt.`ID` LIMIT 0,3;

查询出来的结果

表结构

DROP TABLE IF EXISTS `usertable`; CREATE TABLE `usertable` (   `ID` int(11) NOT NULL AUTO_INCREMENT,   `userName` varchar(120) COLLATE utf8_croatian_ci DEFAULT NULL,   `nikeName` varchar(120) COLLATE utf8_croatian_ci DEFAULT NULL,   `password` varchar(120) COLLATE utf8_croatian_ci DEFAULT NULL,   `salary` double(12,2) DEFAULT NULL,   `commsion_pce` double(2,2) DEFAULT NULL,   `start_time` datetime DEFAULT NULL,   `dept` varchar(20) COLLATE utf8_croatian_ci DEFAULT NULL,   `parent_id` int(11) DEFAULT NULL,   PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COLLATE=utf8_croatian_ci;

数据：

insert  into `usertable`(`ID`,`userName`,`nikeName`,`password`,`salary`,`commsion_pce`,`start_time`,`dept`,`parent_id`) values (1,'AA1','aa1','123',0.00,0.00,'2018-04-21 11:53:08','开发部',95),(2,'AA2','aa2','123',0.00,0.00,'2018-04-21 11:53:08','开发部',1),(3,'AA3','aa3','123',0.00,0.00,'2018-04-21 11:53:08','开发部',2),(4,'AA4','aa4','123',0.00,0.00,'2018-04-21 11:53:08','开发部',95),(5,'AA5','aa5','123',0.00,0.00,'2018-04-21 11:53:08','开发部',4),(6,'AA6','aa6','123',0.00,0.00,'2018-04-21 11:53:08','开发部',5),(7,'AA7','aa7','123',0.00,0.00,'2018-04-21 11:53:08','开发部',5),(8,'BB1','bb1','123',0.00,0.00,'2018-04-21 11:53:40','技术部',95),(9,'BB2','bb2','123',0.00,0.00,'2018-04-21 11:53:40','技术部',8),(10,'BB3','bb3','123',0.00,0.00,'2018-04-21 11:53:40','技术部',8),(11,'BB4','bb4','123',0.00,0.00,'2018-04-21 11:53:40','技术部',9),(12,'BB5','bb5','123',0.00,0.00,'2018-04-21 11:53:40','技术部',9),(13,'BB6','bb6','123',0.00,0.00,'2018-04-21 11:53:40','技术部',10),(14,'BB7','bb7','123',0.00,0.00,'2018-04-21 11:53:40','技术部',10),(15,'CC1','cc1','123',0.00,0.00,'2018-04-21 11:54:14','测试部',95),(16,'CC2','cc2','123',0.00,0.00,'2018-04-21 11:54:14','测试部',15),(17,'CC3','cc3','123',0.00,0.00,'2018-04-21 11:54:14','测试部',16),(18,'CC4','cc4','123',0.00,0.00,'2018-04-21 11:54:14','测试部',16),(19,'CC5','cc5','123',0.00,0.00,'2018-04-21 11:54:14','测试部',16),(20,'CC6','cc6','123',0.00,0.00,'2018-04-21 11:54:14','测试部',16),(21,'CC7','cc7','123',0.00,0.00,'2018-04-21 11:54:14','测试部',16),(95,'king','king','123',0.00,0.00,'2018-04-21 11:54:14','CEO',0);