我使用 Sequel Pro 创建了两个表,每个表都在 MySQL 中填充了不同的过程。虽然每个表在运行相应的过程后都包含正确的信息,但我认为如果我更多地合并一些表,我的数据将不再那么分散。
因此,我想做的是将两个表中的数据合并为一个。下面是我用来尝试实现此目的的代码。不幸的是,它不起作用,并且对代码的任何帮助将不胜感激。
创建表代码:
-- Table: ip_ER_ERA_subtotal
-- DROP TABLE ip_ER_ERA_subtotal;
CREATE TABLE ip_ER_ERA_subtotal
(
Starting_Pitcher VARCHAR(8) NOT NULL,
Game_Date VARCHAR (10) NOT NULL,
Game_Number VARCHAR (1) NOT NULL,
innings_pitched double,
ER double,
ip_total double DEFAULT '0.0',
ER_total double DEFAULT '0.0',
ERA double DEFAULT '0.0',
CONSTRAINT ip_ER_ERA_subtotal_pk
PRIMARY KEY (Starting_Pitcher, Game_Date , Game_Number)
) ENGINE=InnoDB
程序代码:
DELIMITER $$
CREATE PROCEDURE accumulate_IP_ER()
BEGIN
DECLARE pit_id CHAR(10);
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE in_pit REAL;
DECLARE earned_runs REAL;
DECLARE accum REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched
FROM ip_ER_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_subtotal;
INSERT INTO ip_ER_subtotal
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, 0.0
FROM starting_pitchers_game_log;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum := accum + in_pit;
UPDATE ip_ER_subtotal
SET ip_total = accum
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
DECLARE c2 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, earned_runs
FROM ip_ER_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ER_subtotal;
INSERT INTO ip_ER_subtotal
SELECT Starting_Pitcher, Game_Date, Game_Number, ER, 0.0
FROM starting_pitchers_game_log;
SET prev_year := 0;
OPEN c2;
fetch_loop: LOOP
FETCH c2 INTO pit_id, gdate, seq, earned_runs;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum := accum + earned_runs;
UPDATE ip_ER_subtotal
SET ER_total = accum
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c2;
END
我收到以下错误:
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 'DECLARE c2 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, e' at line 46
Here is a screenshot of the first table I already created "ip_subtotal"
Here is a screenshot of the second table I already created "ER_subtotal"
Update:
这基本上是我做了一些小改动的相同代码:
DELIMITER $$
CREATE PROCEDURE accumulate_IP_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE earned_runs INT;
DECLARE accum_ip REAL;
DECLARE accum_er INT;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, earned_runs, accum_ip, accum_er, earned_run_avg;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq
AND prev_year=YEAR;
END LOOP;
CLOSE c1;
END
这是错误:
Incorrect number of FETCH variables
我确实尝试看看如果没有获取任何变量会怎样,并尝试添加“accum_ip”,“accum_er”,“earned_run_avg”,但它不起作用......似乎这八个变量是后者的变量fetch 语句中的三个 + 应该是需要的......
这是表代码:
-- Table: ip_ER_ERA_subtotal
-- DROP TABLE ip_ER_ERA_subtotal;
CREATE TABLE ip_ER_ERA_subtotal
(
Starting_Pitcher VARCHAR(8) NOT NULL,
Game_Date VARCHAR(10) NOT NULL,
Game_Number INT(1) NOT NULL,
innings_pitched double,
ER double,
ip_total double DEFAULT '0.0',
ER_total double DEFAULT '0',
STD_ERA double DEFAULT '0.0',
CONSTRAINT ip_ER_ERA_subtotal_pk
PRIMARY KEY (Starting_Pitcher, Game_Date , Game_Number)
) ENGINE=InnoDB
update:
以下是基于您的更改的代码,但 ER、ER_total 和 STD_ERA 列仅填充“Null”值。
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE ER REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, ER;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
Here's a screenshot of the table:
难道在任何给定的游标操作中只能处理一个公式吗?
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9
感谢您的帮助。
达尔文,
好的,这里是编辑后的代码,它使用正确的值填充以下列:Starting_Pitcher、Game_Date、Game_Number、投球局数和表 ip_ER_ERA_subtotal 中的 ER。 ER_total 和 STD_ERA 列中全部为“0”。
这是代码:
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate VARCHAR(10);
DECLARE seq VARCHAR(1);
DECLARE in_pit REAL;
DECLARE earned_runs REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal
(Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, ER
FROM starting_pitcher_game_log;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, earned_runs;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0.0;
SET earned_run_avg := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER;
SET earned_run_avg := (accum_er / accum_ip) * 9;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND ER = earned_runs
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
我收到以下错误:
Unknown column 'ER' in 'field list'
这是该表的屏幕截图:
现在“ER”列已填充,但不再有“ip_total”
好的,终于可以使用下面的代码了。不确定,但我有一种预感,它不喜欢我们正在调用要插入到新表中的值(starting_pitcher_game_log)的表中的字段“ER”的名称。不知道为什么不喜欢...
编辑:这是我编辑的代码,用于处理“NULL”和被零除的情况:
DELIMITER $$
CREATE PROCEDURE accumulate_ip_ER_ERA()
BEGIN
DECLARE pit_id VARCHAR(8);
DECLARE gdate DATE;
DECLARE seq INT;
DECLARE in_pit REAL;
DECLARE ER_id REAL;
DECLARE accum_ip REAL;
DECLARE accum_er REAL;
DECLARE earned_run_avg REAL;
DECLARE prev_year YEAR(4);
DECLARE end_of_cursor BOOLEAN;
DECLARE c1 CURSOR FOR
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs
FROM ip_ER_ERA_subtotal
ORDER BY Starting_Pitcher, Game_Date, Game_Number;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET end_of_cursor := TRUE;
TRUNCATE TABLE ip_ER_ERA_subtotal;
INSERT INTO ip_ER_ERA_subtotal (Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs)
SELECT Starting_Pitcher, Game_Date, Game_Number, innings_pitched, earned_runs,
IFNULL(innings_pitched, 0), -- replace NULL with 0, if
IFNULL(earned_runs, 0) -- column not initialized
FROM starting_pitcher_game_log;
END IF;
SET end_of_cursor := FALSE;
SET prev_year := 0;
OPEN c1;
fetch_loop: LOOP
FETCH c1 INTO pit_id, gdate, seq, in_pit, ER_id;
IF end_of_cursor THEN
LEAVE fetch_loop;
END IF;
IF YEAR(gdate) != prev_year THEN
SET accum_ip := 0.0;
SET accum_er := 0;
SET earned_run_avg := 0.0;
SET prev_year := YEAR(gdate);
END IF;
SET accum_ip := accum_ip + in_pit;
SET accum_er := accum_er + ER_id;
IF accum_er = 0 THEN -- prevent divide-by-zero
SET earned_run_avg := 0;
ELSE
SET earned_run_avg := (accum_er / accum_ip) * 9;
END IF;
UPDATE ip_ER_ERA_subtotal
SET ip_total = accum_ip,
ER_total = accum_er,
STD_ERA = earned_run_avg
WHERE Starting_Pitcher = pit_id
AND Game_Date = gdate
AND Game_Number = seq;
END LOOP;
CLOSE c1;
END
$$
error:
您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,了解在 '(innings_pitched, 0) 附近使用的正确语法,-- 将 NULL 替换为 0,如果
IFNULL(earned_runs, ' 在第 25 行
更新:
按 STD_ERA 排序时的表屏幕截图,显示该字段范围一端的 STD_ERA 值异常高,或范围另一端的灰色“NULL”值。