- 积分
- 56
- UID
- 16909
- 阅读权限
- 20
- 注册时间
- 2014-11-7
- 精华
- 在线时间
- 小时
- 最后登录
- 1970-1-1
- 职业
- 1
|
现有数据库表结构如下:
tbus_custom_flowdata(
id int,
out int
in int)
说明:id是表的序列,out是销售商ID,in是进货商ID,同时In也是下一级进货商的销售商,相当于out字段。
现在需要从上到下,找一颗树。我的过程如下。发现从第二层开始不对。大侠能否指导下。感谢
drop PROCEDURE IF EXISTS TreeNode;
CREATE PROCEDURE TreeNode (IN rootid INT)
BEGIN
DECLARE Level int ;
drop TABLE IF EXISTS tmpLst;
CREATE TABLE tmpLst (
#id int,
parid int,
childid int,
nLevel int,
sCort varchar(8000)
);
Set Level=0;
INSERT into tmpLst SELECT custom_id_out,custom_id_in,Level,'' FROM tbus_custom_flowdata WHERE custom_id_out=rootid;
WHILE ROW_COUNT()>0 DO
SET Level=Level+1 ;
INSERT into tmpLst
SELECT A.custom_id_out,A.custom_id_in,Level,concat(B.sCort,A.ID)
FROM tbus_custom_flowdata A join tmpLst B
ON A.CUSTOM_ID_OUT =B.childid AND B.nLevel=Level-1 ;
END WHILE;
END;
|
|