mysql - 更新表设置具有复杂条件的列

Question

我已经为此工作了一段时间，这似乎太复杂了。我想要做的是使用以下操作更新列 (x):(p * (100/c) )/100。

p 对应于日期 x 的值，c 对应于日期 x 的值减去一天。

我尝试创建一个带有循环的存储过程，但 select 语句在循环语句中对我不起作用。

这是我的程序，没有更新任何内容:

BEGIN
DECLARE firstqDate,date2 date;
DECLARE p, c float;
DECLARE cpt, val int;
SET @val = 0;
SET @cpt = (select count(*)-1 from quotes);
WHILE (val < 3)  DO
SET @firstqDate = (select qDate from quotes ORDER BY YEAR(qDate) ASC, MONTH(qDate) ASC, DAY(qDate) ASC limit 1,1);
SET date2 = (select qDate from quotes where qDate like DATE_ADD(@firstqDate, INTERVAL 1 DAY );
SET p = (select qOp from quotes where qDate like date2);
SET c = (select qCl from quotes where qDate like DATE_SUB(date2, INTERVAL val DAY));

update quotes
set qCh = (p * (100/c) ) / 100;
set val = val + 1;
end while;
END

编辑:我对存储过程做了一些更新，但仍然没有更新任何行!

BEGIN
DECLARE firstqDate,date2 date;
DECLARE p, c float;
DECLARE cpt, val int;
SET @val = 0;
SET @cpt = (select count(*)-1 from quotes);
SET firstqDate = (select qDate from quotes ORDER BY YEAR(qDate) ASC, MONTH(qDate) ASC, DAY(qDate) ASC limit 1,1);
WHILE (val < 3)  DO
SET date2 = (select qDate from quotes where qDate like DATE_ADD(@firstqDate, INTERVAL val DAY ));
SET p = (select qOp from quotes where qDate like date2);
SET c = (select qCl from quotes where qDate like DATE_SUB(date2, INTERVAL val+1 DAY));
set val = val + 1;
update quotes
set qCh = (p * (100/c) ) / 100
where qOp = p AND qCl = c;
end while;
END

我再次对存储过程进行了一些更新，但没有任何更改。我使用了一些功能。

BEGIN

DECLARE p, c float;
DECLARE cpt, val int;
SET @val = 0;
SET @cpt = (select count(*)-1 from quotes);
WHILE (@val < 3)  DO
SET p = getp(@val, getd());
SET c = getc(@val+1, getd());
set @val = @val + 1;
update quotes
set qCh = (@p * (100/@c) ) / 100
where qOp = @p AND qCl = @c;
end while;
END

功能:获取p:

BEGIN
declare d date;
select qDate into d from quotes ORDER BY YEAR(qDate) ASC, MONTH(qDate) ASC, DAY(qDate) ASC limit 1,1;
return d;
END

获取c:

BEGIN
DECLARE c float;
DECLARE qDa date;
select qDate into qDa from quotes where qDate like DATE_SUB(qD, INTERVAL v DAY );
SELECT qCl INTO c FROM quotes WHERE qDate = qDa;
RETURN c;
END

获取:

BEGIN
declare d date;
select qDate into d from quotes ORDER BY YEAR(qDate) ASC, MONTH(qDate) ASC, DAY(qDate) ASC limit 1,1;
return d;
END

此存储过程必须根据 qDate 的 p 和 qDate 的 c 减去一天来计算所有 qCh。谢谢你!

编辑 - 已解决

哎呀!我终于成功编写了这个存储过程:

BEGIN

DECLARE p, c float;
DECLARE cpt, val int;
SET @val = 0;
SET @cpt = (select count(*)-1 from quotes);
WHILE (@val <= 2)  DO
SET p := getp(@val, getd());
SET c := getc(@val+1, getd());
set @val := @val + 1;
update quotes q
set q.qCh = (getp(@val, getd()) * (100/getc(@val-1, getd())) ) / 100
where q.qOp = getp(@val, getd());
end while;
END

新的getC

BEGIN
DECLARE c float;
DECLARE qDa date;
select qDate into qDa from quotes where qDate like DATE_ADD(qD, INTERVAL v DAY );
SELECT qCl INTO c FROM quotes WHERE qDate = qDa;
RETURN c;
END

我更改了函数 getC 的代码，在第一次迭代中添加 (-1)。现在它正在工作!谢谢大家的帮助!

Answer 1

你的更新周期看起来不错，即使我不明白你为什么要进行 3 个周期。你不应该使用:

WHILE (@val < 3)  DO

而不是

WHILE (val < 3)  DO

？希望对您有帮助

编辑:您需要调试循环才能知道问题出在哪里。

试试这个:

BEGIN
DECLARE @p, @c float;
DECLARE @cpt, @val int;
SET @val = 0;
SET @cpt = (select count(*)-1 from quotes);
SELECT 'Enter Cycle';
WHILE (@val < 3)  DO
SELECT 'In Cyle';
SET @p = getp(@val, getd());
SELECT @p;
SET c = getc(@val+1, getd());
set @val = @val + 1;
update quotes
set qCh = (@p * (100/@c) ) / 100
where qOp = @p AND qCl = @c;
end while;
END

SP 是否打印“Enter Cycle”和“In Cycle”？ p Variable 的值是否打印？这是正确的吗？