以下脚本旨在具有以下功能：
在eurusd_m1中找到一行，其中volume = 250，
在第1行之前找到行
在第一行之后找到第一行
将每行中的所需值复制到obsh4中的单行中
Create procedure doji_result ()
begin
DECLARE initial DATETIME;
DECLARE final DATETIME;
DECLARE centre DATETIME;
DECLARE x int;
SET x = 0;
SET @initial = (select MQLTime from eurusd_m1 where volume=250
order by mqltime asc limit 1);
SET @final = (select MQLTime from eurusd_m1 where volume=250
order by mqltime desc limit 1);
REPEAT
SET @centre = (select MQLTime from eurusd_m1 where volume=250
order by mqltime asc limit x,1);
INSERT INTO obsh4 (MQLrefTime,RrefTime,Open,High,Low,Close,Volume)
select MQLTime,RTime,Open,high,Low,Close,Volume
from eurusd_m1 where MQLTime = @centre
order by MQLTime asc limit 1;
INSERT INTO obsh4 (Open2,High2,Low2,Close2,Volume2)
select Open,high,Low,Close,Volume
from eurusd_m1 where MQLTime < @centre
order by MQLTime desc limit 1;
INSERT INTO obsh4 (Open3,High3,Low3,Close3,Volume3)
select Open,high,Low,Close,Volume
from eurusd_m1 where MQLTime > @centre
order by MQLTime asc limit 1;
SET x=x+1;
UNTIL @centre=@final
END REPEAT;
END $$
DELIMITER ;
当我运行此代码时，来自eurusd_m1的每一行都将被复制到obsh4中的新行（而不是在每次循环迭代时将所有三行都折叠为单行）
我尝试使用UPDATE通过以下脚本添加第二行和第三行数据，但是在FROM上遇到了语法问题，我不确定如何解决这个问题：//
update obsh4
set Open2 = open,
High2 = high,
Low2 = low,
Close2 = close,
Volume2 = volume,
from select Open,high,Low,Close,Volume from eurusd_m1
where
MQLTime < @centre order by MQLTime desc limit 1;
我可以看到三个选项：
1）更新语句需要纠正
2）第二行/第三行的插入需要添加行规范
3）分箱并从头开始生成某些东西。
关于这两个方面的任何建议都将受到欢迎。
谢谢
编辑：循环本身正常运行，并且已独立于当前问题进行了测试。
最佳答案
这段冗长的代码解决了问题。我想我可以简化一些，所以我很乐意提出建议。
drop procedure if exists doji_result;
DELIMITER $$
Create procedure doji_result ()
begin
DECLARE final DATETIME;
DECLARE centre DATETIME;
DECLARE openB1 DOUBLE;
DECLARE highB1 DOUBLE;
DECLARE lowB1 DOUBLE;
DECLARE closeB1 DOUBLE;
DECLARE volumeB1 DOUBLE;
DECLARE openA1 DOUBLE;
DECLARE highA1 DOUBLE;
DECLARE lowA1 DOUBLE;
DECLARE closeA1 DOUBLE;
DECLARE volumeA1 DOUBLE;
DECLARE x int;
SET x = 0;
SET @final = (select MQLTime from eurusd_m1 where volume=250
order by mqltime desc limit 1);
REPEAT
SET @centre = (select MQLTime from eurusd_m1 where volume=250
order by mqltime asc limit x,1);
SET @openB1 = (select open from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @highB1 = (select high from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @lowB1 = (select low from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @closeB1 = (select close from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @volumeB1 = (select volume from eurusd_m1 where MQLtime < @centre order by mqltime desc limit 1);
SET @openA1 = (select open from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @highA1 = (select high from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @lowA1 = (select low from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @closeA1 = (select close from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
SET @volumeA1 = (select volume from eurusd_m1 where MQLtime > @centre order by mqltime asc limit 1);
INSERT INTO obsh4 (MQLrefTime,RrefTime,Open,High,Low,Close,Volume)
select MQLTime,RTime,Open,high,Low,Close,Volume
from eurusd_m1 where MQLTime = @centre
order by MQLTime asc limit 1;
update obsh4
SET open2 = @openB1,
high2 = @highB1,
low2 = @lowB1,
close2 = @closeB1,
volume2 = @volumeB1,
open3 = @openA1,
high3 = @highA1,
low3 = @lowA1,
close3 = @closeA1,
volume3 = @volumeA1
order by mqlreftime desc limit 1;
SET x=x+1;
UNTIL @centre=@final
END REPEAT;
END $$
DELIMITER ;
关于mysql - 将多行插入单行mysql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/25102743/