MySQL 创建存储过程失败但所有内部查询单独成功？

Question

我刚刚在 MySQL 中创建了一个简单的数据库，我正在学习如何编写存储过程。我熟悉 M$SQL，据我所知，以下内容应该有效:

use mydb;
-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER //
CREATE PROCEDURE mydb.doStats ()
BEGIN

CREATE TABLE IF NOT EXISTS resultprobability (
 ballNumber INT NOT NULL ,
 probability FLOAT NULL,
 PRIMARY KEY (ballNumber) );

CREATE TABLE IF NOT EXISTS drawProbability (
 drawDate DATE NOT NULL ,
 ball1 INT NULL ,
 ball2 INT NULL ,
 ball3 INT NULL ,
 ball4 INT NULL ,
 ball5 INT NULL ,
 ball6 INT NULL ,
 ball7 INT NULL ,
 score FLOAT NULL ,
 PRIMARY KEY (drawDate) );

TRUNCATE TABLE resultprobability;

TRUNCATE TABLE drawprobability;

INSERT INTO resultprobability (ballNumber, probability)
(select resultset.ballNumber ballNumber,(count(0)/(select count(0) from resultset)) probability
  from resultset
  group by resultset.ballNumber);

INSERT INTO drawProbability (drawDate, ball1, ball2, ball3, ball4, ball5, ball6, ball7, score)
(select distinct r.drawDate, a.ballnumber ball1, b.ballnumber ball2,
 c.ballnumber ball3, d.ballnumber ball4, e.ballnumber ball5,
 f.ballnumber ball6,g.ballnumber ball7,
((a.probability + b.probability + c.probability + d.probability + e.probability + f.probability + g.probability)/7) score
from resultset r
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 1) a on a.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 2) b on b.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 3) c on c.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 4) d on d.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 5) e on e.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 6) f on f.drawdate = r.drawDate
 inner join (select r.drawDate, r.ballNumber, p.probability from resultset r inner join resultprobability p on p.ballNumber = r.ballNumber where r.appearence = 7) g on g.drawdate = r.drawDate
 order by score desc);

END
//
DELIMITER ;

相反，我得到以下内容

Executed successfully in 0.002 s, 0 rows affected.
Line 1, column 1

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 26
Line 6, column 1

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) probability
  from resultset
  group by resultset.ballNumber);

INSERT INTO d' at line 1
Line 31, column 51

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') score
from resultset r
 inner join (select r.drawDate, r.ballNumber, p.probabi' at line 1
Line 39, column 114

Execution finished after 0.002 s, 3 error(s) occurred.

我做错了什么？我似乎已经用尽了我有限的心智能力!

按照有关删除插入的建议...选择映射并重新运行后，我收到以下错误

Executed successfully in 0.002 s, 0 rows affected.
Line 1, column 1

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 26
Line 6, column 1

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) probability
  from resultset
  group by resultset.ballNumber);

INSERT INTO d' at line 1
Line 31, column 51

Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') score
from resultset r
 inner join (select r.drawDate, r.ballNumber, p.probabi' at line 1
Line 39, column 114

Execution finished after 0.002 s, 3 error(s) occurred.

Answer 1

“所有内部查询单独成功”

不，他们没有，这里的第一个通常的 supect 失败了:

INSERT INTO drawProbability (...) (select...

在选择、重新运行、重新测试、解决下一个语法问题之前丢失“(”。急于找到解决方案不是跳过基本错误检查的理由，尤其是如果你没有运行它们就不要声称已经运行了它们。