BEGINDECLARE v_CkQD_Pn varchar(30);
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl
from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
insert into ck(select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl);OPEN cursor_ckqd;
REPEAT
FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl;
begin
if (stopFlag is null) then
select Sl into v_Rk_Sl from Rk where Pn = v_CkQD_Pn;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn; -- 更新Rk表
end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END出错1
Procedure execution failed
1172 - Result consisted of more than one row出错2
Procedure execution failed
1062 - Duplicate entry '1' for key 'PRIMARY'表ckqd内容
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+
| Id | Pn | Gz | Lb | Sl | Jj | Lj | Dw | Ckt | Gh | Jlr | chr | Bz |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+当我使用过程中语句发现
mysql> select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl;
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| Id | Pn | Gz | Lb | Sl | Jj | Lj | Dw | Ckt | Gh | Jlr | chr | Bz |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
4 rows in set怎么冒出的4行
declare v_CkQD_Sl bigint;
declare stopFlag int;
declare v_Rk_Sl bigint;
DECLARE cursor_ckqd CURSOR
FOR select Pn,Sl
from CkQD;
DECLARE CONTINUE HANDLER FOR NOT FOUND set stopFlag=1;
insert into ck(select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl);OPEN cursor_ckqd;
REPEAT
FETCH cursor_ckqd INTO v_CkQD_Pn,v_CkQD_Sl;
begin
if (stopFlag is null) then
select Sl into v_Rk_Sl from Rk where Pn = v_CkQD_Pn;
if (v_CkQD_Sl <= v_Rk_Sl) then
delete from CkQD where pn = v_CkQD_Pn;
update Rk set Sl = v_Rk_Sl - v_CkQD_Sl where pn = v_CkQD_Pn; -- 更新Rk表
end if;
end if;
end;
UNTIL stopFlag = 1 END REPEAT;
CLOSE cursor_ckqd;
END出错1
Procedure execution failed
1172 - Result consisted of more than one row出错2
Procedure execution failed
1062 - Duplicate entry '1' for key 'PRIMARY'表ckqd内容
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+
| Id | Pn | Gz | Lb | Sl | Jj | Lj | Dw | Ckt | Gh | Jlr | chr | Bz |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+当我使用过程中语句发现
mysql> select ckqd.* from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl;
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| Id | Pn | Gz | Lb | Sl | Jj | Lj | Dw | Ckt | Gh | Jlr | chr | Bz |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
| 1 | 小米 | / | | 1 | 2.2 | 2.8 | 体检科导医台 | 2012-12-21 20:08:19 | 233 | 233 | sdf | |
+----+------+----+----+----+-----+-----+--------------+---------------------+-----+-----+-----+----+
4 rows in set怎么冒出的4行
or
select min(Sl) into v_Rk_Sl from Rk where Pn = v_CkQD_Pn;
1062 - Duplicate entry '1' for key 'PRIMARY'原因就是一行数据查询后变4行的那个,能帮想个办法吗?谢谢了
where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl
group by id
from ckqd,rk where ckqd.Pn = rk.Pn and ckqd.Sl <= rk.Sl