MySQL多表关联查询：按玩家分组统计指定类别剩余题目数

To solve this problem, we need to compute the number of remaining active questions in a specified category for each player by subtracting the number of unique questions they've answered from the total active questions in the category.

Step-by-Step Approach #

1. Calculate Total Active Questions: First, get the count of all active questions in the target category.
2. Count Answered Questions per Player: For each player, count how many distinct active questions they've answered in the category.
3. Compute Remaining Questions: Subtract the answered count from the total to get the remaining questions for each player.

Optimized Query (MySQL 8.0+) #

Using Common Table Expressions (CTEs) for readability and efficiency:

WITH total_active_questions AS (
    -- Get total active questions in the target category (e.g., category_id = 1)
    SELECT COUNT(*) AS total
    FROM questions
    WHERE category_id = 1 
      AND active = 1
),
player_answered_questions AS (
    -- Count distinct active questions each player answered in the category
    SELECT 
        a.player_id,
        COUNT(DISTINCT a.question_id) AS answered_count
    FROM answers a
    JOIN questions q ON a.question_id = q.id
    WHERE q.category_id = 1 
      AND q.active = 1 
      AND a.active = 1
    GROUP BY a.player_id
)
-- Calculate remaining questions for each player
SELECT 
    paq.player_id,
    (taq.total - paq.answered_count) AS Count_of_Remaining_Questions,
    1 AS `Category ID`
FROM player_answered_questions paq
CROSS JOIN total_active_questions taq;

Compatibility Query (Pre-MySQL 8.0) #

If you're using an older MySQL version that doesn't support CTEs, use subqueries instead:

SELECT 
    a.player_id,
    (SELECT COUNT(*) FROM questions WHERE category_id = 1 AND active = 1) - COUNT(DISTINCT a.question_id) AS Count_of_Remaining_Questions,
    1 AS `Category ID`
FROM answers a
WHERE EXISTS (
    SELECT 1 FROM questions q 
    WHERE q.id = a.question_id 
      AND q.category_id = 1 
      AND q.active = 1
)
AND a.active = 1
GROUP BY a.player_id;

Why Your Original Query Failed #

Your initial query had two key issues:

1. Incorrect Join Condition: You joined answers g ON g.id = questions.id—this links the answer ID to the question ID, which is not the correct relationship. The right join is answers.question_id = questions.id.
2. Flawed Filter Logic: The WHERE g.question_id NOT IN (SELECT DISTINCT question_id FROM answers) clause excludes all answers (since every question_id in answers exists in the subquery), leading to incorrect results.

Efficiency Tips #

To make these queries run faster, add the following indexes:

• On questions: (category_id, active, id) – speeds up filtering by category and active status, and joining with answers.
• On answers: (player_id, question_id, active) – optimizes grouping by player and filtering active answers.

内容的提问来源于stack exchange，提问作者Kinshuk Lahiri