mysql中有类似for循环的东西吗？

wydwbb8l 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(431)

我有一个包含比赛信息的表，我需要为每个目标和每个团队返回一行。例如：

+--------+-------+-------+-------+-------+
|  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场比赛，复制粘贴每一行计数的目标数量是一种痛苦。
我想从一个目标表开始，该表预先填充了与我已经拥有的一样多的信息。

mysql mysql-workbench

来源：https://stackoverflow.com/questions/53855103/is-there-something-like-a-for-loop-in-mysql

2条答案

按热度按时间

kcugc4gi1#

创建一个表，其中包含从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

展开查看全部

赞(0）回复(0）举报 2021-06-16

soat7uwm2#

mysql中的while循环语法示例：

delimiter //
CREATE procedure yourdatabase.while_example()
wholeblock:BEGIN
  declare str VARCHAR(255) default '';
  declare x INT default 0;
  SET x = 1;
  WHILE x <= 5 DO
    SET str = CONCAT(str,x,',');
    SET x = x + 1;
  END WHILE;
  select str;
END//

打印内容：

mysql> call while_example();
+------------+
| str        |
+------------+
| 1,2,3,4,5, |
+------------+

mysql中的for循环语法示例：

delimiter //
CREATE procedure yourdatabase.for_loop_example()
wholeblock:BEGIN
  DECLARE x INT;
  DECLARE str VARCHAR(255);
  SET x = -5;
  SET str = '';
  loop_label: LOOP
    IF x > 0 THEN
      LEAVE loop_label;
    END IF;
    SET str = CONCAT(str,x,',');
    SET x = x + 1;
    ITERATE loop_label;
  END LOOP;
  SELECT str;
END//

打印内容：

mysql> call for_loop_example();
    +-------------------+
    | str               |
    +-------------------+
    | -5,-4,-3,-2,-1,0, |
    +-------------------+
    1 row in set (0.00 sec)

辅导的：http://www.mysqltutorial.org/stored-procedures-loop.aspx

展开查看全部

赞(0）回复(0）举报 2021-06-15

我来回答

mysql中有类似for循环的东西吗？

2条答案

相关问题

热门标签

最新问答