MYSQL 版本:5.1.41
结构和测试数据
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3`(
`id` INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`pid` INT(1) NOT NULL DEFAULT '1',
`qty` INT(1) NOT NULL DEFAULT '0',
CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t3`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
INSERT INTO `t3`(`id`,`pid`,`qty`) VALUES
(1,1,0),
(2,1,1),
(3,2,1),
(4,1,1),
(5,2,1),
(6,3,1),
(7,6,1),
(8,2,1);
存储函数
DELIMITER $$
DROP FUNCTION IF EXISTS `getParent`$$
CREATE FUNCTION `getParent`(iid INT)
RETURNS VARCHAR(1000)
READS SQL DATA
BEGIN
DECLARE str VARCHAR(1000);
SET str=CAST(iid AS CHAR); WHILE iid IS NOT NULL DO
SELECT `pid`,CONCAT_WS(',', str, iid) INTO iid,str FROM `t3` WHERE `id`=iid AND `pid`>1;
END WHILE;
RETURN str;
END$$
DELIMITER ;执行下面命令没错误,但程序停住,应该是进入死循环或者哪里错了
SELECT getParent(7);期望结果
7,6,3,2
结构和测试数据
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3`(
`id` INT(1) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`pid` INT(1) NOT NULL DEFAULT '1',
`qty` INT(1) NOT NULL DEFAULT '0',
CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `t3`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
)ENGINE=InnoDB;
INSERT INTO `t3`(`id`,`pid`,`qty`) VALUES
(1,1,0),
(2,1,1),
(3,2,1),
(4,1,1),
(5,2,1),
(6,3,1),
(7,6,1),
(8,2,1);
存储函数
DELIMITER $$
DROP FUNCTION IF EXISTS `getParent`$$
CREATE FUNCTION `getParent`(iid INT)
RETURNS VARCHAR(1000)
READS SQL DATA
BEGIN
DECLARE str VARCHAR(1000);
SET str=CAST(iid AS CHAR); WHILE iid IS NOT NULL DO
SELECT `pid`,CONCAT_WS(',', str, iid) INTO iid,str FROM `t3` WHERE `id`=iid AND `pid`>1;
END WHILE;
RETURN str;
END$$
DELIMITER ;执行下面命令没错误,但程序停住,应该是进入死循环或者哪里错了
SELECT getParent(7);期望结果
7,6,3,2
SELECT `pid`,CONCAT_WS(',', str, iid) INTO iid,str FROM `t3` WHERE `id`=iid AND `pid`>1;这个语句的结果会是什么吗? 自己试着分析一下
根据某个节点,查找到所有父节点(最好可以指定最高的父节点,例如本例查找到顶级节点的子节点)还有,像上面的存储过程,我想实现的是更新除顶级节点外的父节点的qty字段
是想把所有父节点ID都查询出来,然后UPDATE `t3` SET `qty`=`qty`+xx WHERE `id` IN(str)
还是每次循环都直接UPDATE(也就是把存储函数改成存储过程,不回显)比较好呢??
我尝试
SET @iid=2;
SELECT `pid` INTO @iid FROM `t3` WHERE `id`=@iid AND `pid`>1;
得到
Warning | 1329 | No data - zero rows fetched, selected, or processed
警告
修改成这样是可以了
DELIMITER $$
DROP FUNCTION IF EXISTS `getParent`$$
CREATE FUNCTION `getParent`(iid INT)
RETURNS VARCHAR(1000)
READS SQL DATA
BEGIN
DECLARE str VARCHAR(1000) DEFAULT CAST(iid AS CHAR(1000));
DECLARE parent INT(1); WHILE iid IS NOT NULL DO
SET parent=(SELECT `pid` FROM `t3` WHERE `id`=iid AND `pid`>1);
SET iid=IF(parent IS NOT NULL, parent, NULL);
SET str=CONCAT_WS(',', str, iid);
END WHILE;
RETURN str;
END$$
DELIMITER ;能否回答下我2楼提的问题呢??