我的表结构是
pid id ppid
1 a b
2 b c
3 c d
4 q z
5 d m
现在要用id ='a' 的这条记录查最终的 ppid 就是说用a 找到 m这个就对了 这个语句应该怎么写,如果用存储过程应该怎么写,小弟刚接触mysql 请大侠们指教下
pid id ppid
1 a b
2 b c
3 c d
4 q z
5 d m
现在要用id ='a' 的这条记录查最终的 ppid 就是说用a 找到 m这个就对了 这个语句应该怎么写,如果用存储过程应该怎么写,小弟刚接触mysql 请大侠们指教下
的思路如下。select ppid in @a from yourTable where id = @a
然后当@a非空时,继续循环,你先自己试着写一下,有具体问题可以贴出来你的代码和错误信息再来问。下面是mySQL的参考手册
http://dev.mysql.com/doc/refman/5.1/zh/index.html
CREATE DEFINER=`cessoftuser`@`%` PROCEDURE `BOMTREE`(`vid` VARCHAR(2))
BEGIN
DECLARE CURLEVEL INT DEFAULT 2;
CREATE TEMPORARY TABLE IF NOT EXISTS `tmp1`(
`id` VARCHAR(2),
`pid` VARCHAR(2),
`ppid` int)
ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TEMPORARY TABLE IF NOT EXISTS `tmp2`(
`id` VARCHAR(2),
`pid` VARCHAR(2),
`ppid` int)
ENGINE=InnoDB DEFAULT CHARSET=utf8;TRUNCATE TABLE `tmp1`;
TRUNCATE TABLE `tmp2`;INSERT INTO `tmp1`(id,pid,ppid)
SELECT 1,id,ppid
FROM t1 WHERE id= vid ORDER BY id,ppid;INSERT INTO `tmp2` SELECT * FROM `tmp1`;
IF ROW_COUNT()>0 THEN
LEVEL1: WHILE CURLEVEL <= 6 DO
INSERT INTO `tmp1`(id,pid,ppid)
SELECT CURLEVEL,id,ppid
FROM t1 a,`tmp2` b where b.id = a.ppid ORDER BY a.id,a.ppid; DELETE FROM `tmp2`;
INSERT INTO `tmp2` SELECT * FROM `tmp1` WHERE pid = CURLEVEL; IF ROW_COUNT()=0 THEN LEAVE LEVEL1; END IF;
SET CURLEVEL=CURLEVEL+1;
END WHILE LEVEL1;
END IF;END $$DELIMITER ;