I have been trying many different ways to solve this problem from this forum and from many others. I can't seem to find a solution to this problem or any documentation that will give me a straight answer.

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

I wondered if you could have a look at it for me.

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

Thanks

谢谢

THE PROBLEM:

问题：

I've got a database with the following tables participant_scores leagues rounds

我有一个数据库，其中包含以下参与者参与各种各样的联赛

I am currently able to display the scores of a single round, one round at a time... which is exactly how I want it. But I also want to display the score that each participant got for all rounds. Lets say we have 2 rounds. I want the output on my results screen to look something like this:

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

 Currently viewing league 20, Round 1
 of 2:

 User Name |  Score   |  Total Score

 Tom       |   10     |    200

 James     |   50     |    300

username - the participant's name score = the score for this current round total score = all of the round scores for this league added together.

用户名 - 参与者的姓名得分=当前回合总得分的得分=此联赛的所有回合得分加在一起。

My Mysql query is below. Apologies for the messyness of it, I've rewritten it about 100 times and this current way is the only way that works fully.

我的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

1 个解决方案

#1

What you want here is a subquery in this form:

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

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

The subquery as a column will be calculated for each row you return from your initial query.

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

To try to add it into your query:

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

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

I was a little worried about the subquery including multiple leagues, but it looks like a single participant can only belong to one league anyway. You might need to include something in the subquery to check for this.

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

#1