MySQL：如何组合多个 SELECT 查询，在同一个表上使用不同的 WHERE 条件？

MySQL: How to Combine multiple SELECT queries, using different WHERE criteria on the same table?

提问by Tom

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)

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

回答by Kieveli

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.

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