CREATE TABLE `treenodes` (
`infoID` varchar(50) NOT NULL,
`infoName` varchar(50) NOT NULL,
`TuijianID` varchar(50) NOT NULL,
`ParentID` varchar(50) NOT NULL,
`gongpaiparentId` varchar(50) NOT NULL,
`weizhi` int(11) NOT NULL,
`tueName` varchar(50) NOT NULL,
`Idcard` varchar(20) NOT NULL,
`Password` varchar(50) NOT NULL,
`Transaction` varchar(50) NOT NULL,
`Emailadd` varchar(100) NOT NULL,
`banName` varchar(100) NOT NULL,
`BankCardNum` varchar(50) NOT NULL,
`Bankkaihuren` varchar(20) NOT NULL,
`BankkaihuXin` varchar(20) NOT NULL,
`Qqnum` varchar(20) NOT NULL,
`Balance` float NOT NULL default '0',
`Income` float NOT NULL default '0',
`zhucetime` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`zhuangtai` int(11) NOT NULL default '1',
PRIMARY KEY (`infoID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `treenodes` VALUES ('0001', '1231', '123', '0', '11', 11, '11', '11', '1', '1', '1', '1', '1', '1', '1', '', 0, 0, '2010-05-22 00:48:56', 1);
INSERT INTO `treenodes` VALUES ('0002', '111', '111', '0001', '1', 1, '11', '1', '1', '1', '1', '11', '1', '1', '1', '1', 0, 0, '2010-05-22 00:48:56', 1); CREATE PROCEDURE showChildTreeList (IN rootId varchar(50))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpList
(sno int primary key auto_increment,id varchar(50),depth int);
DELETE FROM tmpList;
CALL createTreeChildList(rootId,0);
select a.depth,b.infoid,b.infoname,b.parentid from tmpList a ,treenodes b where a.id=b.infoid order by a.sno;
END;//
CREATE PROCEDURE createTreeChildList(IN rootId varchar(50),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT infoid FROM treenodes where parentid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; insert into tmpList values (null,rootId,nDepth); if nDepth<10 then
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createTreeChildList(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
end if ;END;//
infoid 是主键id parentid 是父节点列
call showChildTreeList ('0001');
只能查询到自己 0001 数据而子记录 0002 没有查不来 我试了如果 就 infoname,infoid,parentid 3个列名 就没问题
困恼我一晚上了。帮忙解决是哪里出了问题。
`infoID` varchar(50) NOT NULL,
`infoName` varchar(50) NOT NULL,
`TuijianID` varchar(50) NOT NULL,
`ParentID` varchar(50) NOT NULL,
`gongpaiparentId` varchar(50) NOT NULL,
`weizhi` int(11) NOT NULL,
`tueName` varchar(50) NOT NULL,
`Idcard` varchar(20) NOT NULL,
`Password` varchar(50) NOT NULL,
`Transaction` varchar(50) NOT NULL,
`Emailadd` varchar(100) NOT NULL,
`banName` varchar(100) NOT NULL,
`BankCardNum` varchar(50) NOT NULL,
`Bankkaihuren` varchar(20) NOT NULL,
`BankkaihuXin` varchar(20) NOT NULL,
`Qqnum` varchar(20) NOT NULL,
`Balance` float NOT NULL default '0',
`Income` float NOT NULL default '0',
`zhucetime` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
`zhuangtai` int(11) NOT NULL default '1',
PRIMARY KEY (`infoID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `treenodes` VALUES ('0001', '1231', '123', '0', '11', 11, '11', '11', '1', '1', '1', '1', '1', '1', '1', '', 0, 0, '2010-05-22 00:48:56', 1);
INSERT INTO `treenodes` VALUES ('0002', '111', '111', '0001', '1', 1, '11', '1', '1', '1', '1', '11', '1', '1', '1', '1', 0, 0, '2010-05-22 00:48:56', 1); CREATE PROCEDURE showChildTreeList (IN rootId varchar(50))
BEGIN
CREATE TEMPORARY TABLE IF NOT EXISTS tmpList
(sno int primary key auto_increment,id varchar(50),depth int);
DELETE FROM tmpList;
CALL createTreeChildList(rootId,0);
select a.depth,b.infoid,b.infoname,b.parentid from tmpList a ,treenodes b where a.id=b.infoid order by a.sno;
END;//
CREATE PROCEDURE createTreeChildList(IN rootId varchar(50),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT infoid FROM treenodes where parentid=rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; insert into tmpList values (null,rootId,nDepth); if nDepth<10 then
OPEN cur1;
FETCH cur1 INTO b;
WHILE done=0 DO
CALL createTreeChildList(b,nDepth+1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
end if ;END;//
infoid 是主键id parentid 是父节点列
call showChildTreeList ('0001');
只能查询到自己 0001 数据而子记录 0002 没有查不来 我试了如果 就 infoname,infoid,parentid 3个列名 就没问题
困恼我一晚上了。帮忙解决是哪里出了问题。
解决方案 »
- [收集]mysql 无法联接故障现象及原因
- mysql 数据库中被恶意注册上了特殊字符,有没有影响?
- mysql中的中文数据,用SQLyog导出后全部显示为问号!
- mysql如何为下面的单表添加索引,提高查询效率(重新提问)
- shell脚本连接mysql
- 请问数据记录最低达到多少条,考虑分表呢?
- 如何处理分组后的值匹配
- SELECT in a stored procedure must have INTO这句话的意思是什么?
- 问一下:ms-sql 用的的是Transaction-sql,那么mysql用的是什么?
- 高手请进, 解决必给高分!!!!
- 未找到匹配的结果,能否让查询字段返回0?
- 改个mysql过程
mysql> select * from tmpList;
-> //
+-----+------+-------+
| sno | id | depth |
+-----+------+-------+
| 1 | 0001 | 0 |
| 2 | 2 | 1 |
+-----+------+-------+
2 rows in set (0.00 sec)
第2次插入的是int,显然:
存储过程定义:
CREATE PROCEDURE createTreeChildList(IN rootId varchar(50),IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
中的b应该定义成varchar(50),而不是int。
不管如何,存储过程也得反推进行相关"debug"。改了以后,结果如下:
mysql> call showChildTreeList ('0001');
-> //
+-------+--------+----------+----------+
| depth | infoid | infoname | parentid |
+-------+--------+----------+----------+
| 0 | 0001 | 1231 | 0 |
| 1 | 0002 | 111 | 0001 |
+-------+--------+----------+----------+
2 rows in set (0.00 sec)Query OK, 0 rows affected, 1 warning (0.00 sec)
`infoID` varchar(50) NOT NULL,
`infoName` varchar(50) NOT NULL,
`TuijianID` varchar(50) NOT NULL,
`ParentID` varchar(50) NOT NULL,
你这儿都是 varchar(50) 啊。 要么改这儿的定义,要么改过程中的定义。