如何在MySQL中合并两次SELECT COUNT结果筛选符合条件的玩家

Hey there! Let's work through this SQL problem for your Mastermind game's challenge mode. I get that you need to calculate each player's total wins (both as challenger and challenged) and filter those with over 20 wins—all in pure SQL for efficiency.

First, let's recap the win rules to make sure we're aligned:

• A challenger wins if their number of tries is less than or equal to the challenged player's tries (since ties count as challenger wins)
• The challenged player wins only if the challenger's tries are greater than theirs

Step-by-Step SQL Approach #

We'll break this into two parts: calculating wins for players in each role, then combining and aggregating the results.

Here's the complete SQL query:

SELECT username, SUM(wins) AS total_wins
FROM (
    -- Count wins where the player was the challenger (including ties)
    SELECT usernameChallenger AS username, COUNT(*) AS wins
    FROM Challenge
    WHERE numberOfTriesChallenger <= numberOfTriesChallenged
    GROUP BY usernameChallenger
    
    UNION ALL
    
    -- Count wins where the player was the challenged one
    SELECT usernameChallenged AS username, COUNT(*) AS wins
    FROM Challenge
    WHERE numberOfTriesChallenger > numberOfTriesChallenged
    GROUP BY usernameChallenged
) AS all_player_wins
GROUP BY username
HAVING total_wins > 20
ORDER BY total_wins DESC;

How This Works #

1. Challenger Wins Calculation:

   • The first subquery targets players in the usernameChallenger role. We count every challenge where their try count is ≤ the challenged player's (covers both outright wins and ties, which count as challenger wins).

2. Challenged Player Wins Calculation:

   • The second subquery targets players in the usernameChallenged role. We count every challenge where the challenger's try count is > theirs—this is the only scenario where the challenged player wins.

3. Combine & Aggregate:

   • UNION ALL merges the two result sets (we use ALL to preserve all rows, even if a player appears in both roles).
   • The outer query groups by username, sums up all their wins from both roles, filters for players with total wins > 20, and sorts results by total wins descending for readability.

Key Notes #

• This query leverages your existing Challenge table structure efficiently, avoiding any application-level computation (which aligns with your goal for better performance).
• The table's primary key ensures no duplicate challenge records, so COUNT(*) will accurately count each valid challenge outcome.

内容的提问来源于stack exchange，提问作者Nick L