嗯这样可以 是我想要的结果insert就不做了。就先拿这五条固定数据做测试要不我提供下我写的测试存储过程,这中间好像有个BUG,没有循环更新balance值。但我看了代码又好像没觉得哪有问题 drop PROCEDURE if exists test; CREATE PROCEDURE test() begin declare i integer default 10; declare d_pid int default 0; declare d_pid_in int default 0; declare str_temp varchar(40) default '';select `parentid` into d_pid from tgy_members where id=4; update tgy_members set balance=100 where id=4; #上一级loop1: LOOP begin select `parentid` into d_pid_in from tgy_members where id=d_pid; set str_temp = concat(str_temp,d_pid);
if(d_pid_in = 0) then set str_temp = concat(str_temp,d_pid_in); update tgy_members set balance=i where id=d_pid_in;
leave loop1; else set str_temp = concat(str_temp,d_pid_in);
update tgy_members set balance=i where id=d_pid_in; select `parentid` into d_pid from tgy_members where id=d_pid_in; end if; set i=i+1;
end; END LOOP loop1; select str_temp;end 我测试时没有传参数,然后balance值先没按比例去弄。主要是先把循环update解决
update tgy_members set balance=100 where id=4; #上一级这里应该是id=5 先从5开始
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
2. balance字段更新为什么?全部更新为数字1行不行?
另外 按照你的例子。 传入两个值5(ID),1000(总额)
期望的结果是什么? 下面是你想要的结果吗?id parentid balance
1 0 100
2 1 200
3 2 300
4 3 400
5 4 1000
问题说明越详细,回答也会越准确!参见如何提问。(提问的智慧)
drop PROCEDURE if exists test;
CREATE PROCEDURE test()
begin
declare i integer default 10;
declare d_pid int default 0;
declare d_pid_in int default 0;
declare str_temp varchar(40) default '';select `parentid` into d_pid from tgy_members where id=4;
update tgy_members set balance=100 where id=4; #上一级loop1: LOOP
begin
select `parentid` into d_pid_in from tgy_members where id=d_pid;
set str_temp = concat(str_temp,d_pid);
if(d_pid_in = 0) then
set str_temp = concat(str_temp,d_pid_in);
update tgy_members set balance=i where id=d_pid_in;
leave loop1;
else
set str_temp = concat(str_temp,d_pid_in);
update tgy_members set balance=i where id=d_pid_in;
select `parentid` into d_pid from tgy_members where id=d_pid_in;
end if;
set i=i+1;
end;
END LOOP loop1;
select str_temp;end
我测试时没有传参数,然后balance值先没按比例去弄。主要是先把循环update解决
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for tgy_members
-- ----------------------------
DROP TABLE IF EXISTS `tgy_members`;
CREATE TABLE `tgy_members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parentId` int(11) DEFAULT NULL,
`UserName` varchar(30) NOT NULL,
`UserType` tinyint(4) NOT NULL,
`TrueName` varchar(30) NOT NULL,
`TK_Password` varchar(30) DEFAULT NULL,
`CardNo` varchar(20) DEFAULT NULL,
`CardPic` varchar(50) DEFAULT NULL,
`Email` varchar(60) DEFAULT NULL,
`Phone` varchar(20) DEFAULT NULL,
`Balance` decimal(10,0) DEFAULT NULL,
`State` tinyint(4) DEFAULT NULL,
`CreateTime` datetime DEFAULT NULL,
`LastTime` datetime DEFAULT NULL,
`ClickTime` datetime DEFAULT NULL,
`LoginCount` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `UserName` (`UserName`),
KEY `parentId` (`parentId`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records
-- ----------------------------
INSERT INTO `tgy_members` VALUES ('1', '0', 'test', '0', 'test', '123', null, null, null, null, '0', null, null, null, null, null);
INSERT INTO `tgy_members` VALUES ('2', '1', 'test2', '0', 'test', '123', null, null, null, null, '0', null, null, null, null, null);
INSERT INTO `tgy_members` VALUES ('3', '2', 'test3', '0', 'test', '123', null, null, null, null, '0', null, null, null, null, null);
INSERT INTO `tgy_members` VALUES ('4', '3', 'test4', '0', 'test', '123', null, null, null, null, '0', null, null, null, null, null);
INSERT INTO `tgy_members` VALUES ('5', '4', 'test', '0', 'test', null, null, null, null, null, '0', null, null, null, null, null);哦好的,我提供了,你帮我测下看.过程就是那个
我只是定5层而已,超过5层是必然的,所以我才说是无限循环。循环到parentid=0为止
+----+----------+----------+---------+
| id | parentId | UserName | Balance |
+----+----------+----------+---------+
| 1 | 0 | test | 0 |
| 2 | 1 | test2 | 0 |
| 3 | 2 | test3 | 0 |
| 4 | 3 | test4 | 0 |
| 5 | 4 | test | 0 |
+----+----------+----------+---------+
5 rows in set (0.00 sec)mysql> drop PROCEDURE x;
Query OK, 0 rows affected (0.17 sec)mysql> delimiter //
mysql> CREATE PROCEDURE x(vid int, vAmt int)
-> begin
-> DECLARE i INT default 4;
-> update tgy_members set Balance=vAmt where id= vid;
-> select parentId into vid from tgy_members where id= vid;
-> WHILE vid > 0 DO
-> update tgy_members set Balance=vAmt*i/10 where id= vid;
-> select parentId into vid from tgy_members where id= vid;
-> set i=i-1;
-> END WHILE;
-> end
-> //
Query OK, 0 rows affected (0.06 sec)mysql> delimiter ;
mysql> call x(5,100);
Query OK, 0 rows affected (0.23 sec)mysql> select id,parentId,UserName,Balance from tgy_members;
+----+----------+----------+---------+
| id | parentId | UserName | Balance |
+----+----------+----------+---------+
| 1 | 0 | test | 10 |
| 2 | 1 | test2 | 20 |
| 3 | 2 | test3 | 30 |
| 4 | 3 | test4 | 40 |
| 5 | 4 | test | 100 |
+----+----------+----------+---------+
5 rows in set (0.00 sec)mysql>
break;
end if;应该是这样的一个逻辑关系,不过我不知道MYSQL的退出循环怎么写