MYSQL For/While循环

Question

在MYSQL中可以执行For或While循环吗？

我得到了以下代码摘要，但是完整的代码会用到home_id_15、home_score_15、away_id_15和away_score_15：

$query3 = '
SELECT match_date, fixture_id, COUNT(a.home) AS home, SUM(a.points) AS points FROM
    (  
      SELECT match_date, fixture_id, home_id_1 AS home, home_score_1 AS points FROM scores
      WHERE home_id_1 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, away_id_1 AS home, away_score_1 AS points 
      FROM scores
      WHERE away_id_1 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, home_id_2 AS home, home_score_2 AS points 
      FROM scores
      WHERE home_id_2 =' .intval($_REQUEST['ID']).'  
      UNION ALL  
      SELECT match_date, fixture_id, away_id_2 AS home, away_score_2 AS points 
      FROM scores
      WHERE away_id_2 =' .intval($_REQUEST['ID']).'  
      UNION ALL) a  
     GROUP BY match_date'

第一和第二子选择基本上被重复，直到它们达到15。

这似乎有点长，我想知道是否可以在MYSQL中使用循环来输出

home_id_1, home_score_1, away_id_1, away_score_1 [up to] home_id_15, home_score_15, away_id_15, away_score_15

分别？

谢谢丹。

Answer 1

看起来您可能需要对数据库进行更多的规范化。假设你每行得了6分。不要让每个分数都成为一列，而是单独创建一个名为“分数”的表，或者类似于外键列和得分列的表。然后用这个分数表加入这个表。

示例：

• 表:小组

- team\_id
- name

​

• 表:分数

- team\_id
- score

​

SELECT t.*, s.score 
FROM team t
join scores s 
on t.team_id=s.team_id;

Todo:将matches的概念添加到您的模式和Join中