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

Question

我已经在这上面工作了一段时间了，这似乎太复杂了。我想做的是用(p * (100/c) ) / 100操作更新一个列(x)。

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

我试图用loop创建一个存储过程，但是select语句在loop语句中对我不起作用。

以下是我的过程，它不会更新任何内容：

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

函数: get 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

getd：

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的代码更改为add (-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 Cyle”？是否打印p变量的值？是这样的吗？