mysql - MySql中有类似FOR循环的东西吗？

Question

我有一个包含比赛信息的表格，我需要为每个进球和每个球队返回一行。例如:

+--------+-------+-------+-------+-------+
|  Match | Team1 | goal1 | goal2 | Team2 |
+--------+-------+-------+-------+-------+
|    1   | Red   |   1   |   0   | Blue  |   
+--------+-------+-------+-------+-------+
|    2   | Green |   2   |   1   | Black |   
+--------+-------+-------+-------+-------+

我想为每一行运行一个函数，为每个团队的每个目标返回一行。所以我的函数结果是:

+--------+-------+-------+
|  Goal  | Match | Team  |
+--------+-------+-------+
|    1   |   1   |  Red  |  
+--------+-------+-------+
|    2   |   2   | Green | 
+--------+-------+-------+
|    3   |   2   | Green |  
+--------+-------+-------+
|    4   |   2   | Black | 
+--------+-------+-------+

我的最终目标是，我需要为每场比赛/球队/进球留出一行来手动填写得分手和分钟。由于我有超过 40000 场比赛，复制粘贴每一行来计算进球数是一件痛苦的事情。

我想从一个目标表开始，其中预先填充了我已有的尽可能多的信息。

Answer 1

创建一个表格，其中包含从 1 到可能目标的最大数量的数字，即

CREATE TABLE numbers (
    num INT PRIMARY KEY
);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), ...

然后您可以将此表与原始表连接起来:

SELECT num AS Goal, `Match`, Team
FROM numbers
JOIN (
    SELECT Team1 AS Team, goal1 AS goals, `Match`
    FROM matches
    UNION
    SELECT Team2 AS Team, goal2 AS goals, `Match`
    FROM matches
) ON num <= goals