我一直在尝试许多不同的方法来从这个论坛和许多其他论坛中解决这个问题。我似乎找不到这个问题的解决方案或任何能给我直接答案的文档。

我想知道你是否可以帮我看一下。

谢谢

问题:

我有一个包含下表的数据库 参与者分数 联赛 回合

我目前能够显示单轮的分数，一次显示一轮……这正是我想要的。 但我也想显示每个参与者在所有回合中获得的分数。 假设我们有 2 轮。我希望结果屏幕上的输出看起来像这样:

 Currently viewing league 20, Round 1
 of 2:
 
 User Name |  Score   |  Total Score
 
 Tom       |   10     |    200
 
 James     |   50     |    300

username - 参与者的名字 score = 本轮得分 总得分 = 本联赛所有回合得分加在一起。

我的 Mysql 查询如下。对它的困惑表示歉意，我已经重写了大约 100 次，目前这种方式是唯一可以完全工作的方式。

>> league_participants_query (mysql)

    # FIELDS
    SELECT    
        participants.participant_id,                                          # ID - used for functions
        participants.participant_name,                                        # NAME
        participants.participant_gender,                                      # Participant info             
        classes.class_name,                                                   # Class name
        schools.school_name,                                                  # School name
        participant_scores.participant_score,                                 # Participant score
        participant_scores.participant_score_id



    # TABLES
    FROM         participant_scores, participants, classes, league_schools, schools, leagues, rounds    


    # filter leagues
    WHERE        leagues.league_id               =     51

    AND          rounds.league_id                =     51    # the current league we are viewing
    AND          rounds.round_id                 =     25    # the current round of the league we are viewing

    # filter league schools
    AND          participant_scores.round_id     =     25    # the current round of the league we are viewing

    # filter schools allowed
    AND          league_schools.league_id        =     51    # the current league we are viewing

    # Filter schools
    AND          schools.school_id               =     league_schools.school_id

    # Filter classes
    AND          classes.school_id               =     schools.school_id                                        
    AND          classes.year_group_id           =     leagues.year_group_id

    # Filter participants
    AND          participants.class_id           =     classes.class_id

    # Filter participant_scores
    AND          participant_scores.participant_id   =      participants.participant_id

    #Grouping
    GROUP BY     participants.participant_id

最佳答案

你想要的是这种形式的子查询:

SELECT
  name,
  round,
  score,
  ( select sum( score ) from scores sc where sc.userid = users.userid ) total
FROM users INNER JOIN scores on users.userid = scores.scoreid

将为您从初始查询返回的每一行计算作为列的子查询。

尝试将其添加到您的查询中:

SELECT
  participants.participant_id,
  participants.participant_name,
  participants.participant_gender,
  classes.class_name,
  schools.school_name,
  participant_scores.participant_score,
  participant_scores.participant_score_id,
  ( SELECT sum(participant_score) FROM participant_scores tbl_scores2
    WHERE tbl_scores2.participant_score_id = participants.participant_id ) total
FROM participant_scores, participants, classes,
  league_schools, schools, leagues, rounds
WHERE
  leagues.league_id = 51  AND
  rounds.league_id = 51  AND
  rounds.round_id = 25 AND
  participant_scores.round_id  = 25  AND
  league_schools.league_id = 51  AND
  schools.school_id = league_schools.school_id  AND
  classes.school_id = schools.school_id  AND
  classes.year_group_id = leagues.year_group_id  AND
  participants.class_id = classes.class_id  AND
  participant_scores.participant_id = participants.participant_id 
GROUP BY
  participants.participant_id

我有点担心包含多个联赛的子查询，但看起来单个参与者无论如何只能属于一个联赛。您可能需要在子查询中包含一些内容来检查这一点。

关于MySQL:如何在同一个表上使用不同的 WHERE 条件来组合多个 SELECT 查询？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/925901/