MYSQL过程:循环访问文本列的不同值并创建临时表

Question

我的子组表有

subgroup_abbr_str
AA
BB
BB
CC
DD
DD
DD
... and many more

我想为每个值创建一个临时表。临时表 AA、BB、CC 和 DD。这是我创建表变量而不是 AA 的初始代码。

CREATE PROCEDURE FLAGITERATION()
BEGIN
DECLARE totRows INT DEFAULT 0;
DECLARE startRow INT DEFAULT 0;
DECLARE var TEXT DEFAULT  NULL;
SELECT COUNT(*) FROM subgroups INTO totRows ;
SET startRow=0;
WHILE startRow<totRows DO 
SELECT DISTINCT subgroup_abbr_str FROM subgroups LIMIT startRow,1 INTO var ;
CREATE TEMPORARY TABLE var AS SELECT SUM(present)
FROM trad_new;
SET startRow = startRow + 1;
END WHILE;
End;
;;

Answer 1

为了纠正编程错误，使用所有表重建示例数据需要更多时间

你想做的事情可以用动态SQL来完成。

此外，您应该使用光标进行循环

CREATE TABLE subgroups (subgroup_abbr_str VaRCHAR(2));
INSERT INTO subgroups VALUES ('AA'),
('BB'),
('BB'),
('CC'),
('DD'),
('DD'),
('DD')

CREATE tABLE trad_new (present INT)

INSERT INTO trad_new VALUEs (1)

CREATE PROCEDURE FLAGITERATION()
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE _subgroup varchar(100) DEFAULT "";
-- declare cursor for subgroup_abbr_str
DEClARE cursubgroup 
CURSOR FOR 
SELECT DISTINCT subgroup_abbr_str FROM subgroups;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER 
FOR NOT FOUND SET finished = 1;

OPEN cursubgroup;
getsubgroup: LOOP
FETCH cursubgroup INTO _subgroup;
IF finished = 1 THEN 
LEAVE getsubgroup;
-- build taböe
SET @sql = CONCAT("CREATE TEMPORARY TABLE ",_subgroup," AS SELECT SUM(present)
FROM trad_new;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
END LOOP getsubgroup;
CLOSE cursubgroup;  
DEALLOCATE PREPARE stmt;  
End

call FLAGITERATION()

SELECT * FROM AA;

|总和(现在) | |-----------: | |           1 |

db<>小提琴在这里