MariaDB多表关联查询：筛选玩家已达成的完整成就

要实现这个需求，核心是先确认玩家完成了某成就的全部动作，再关联对应的动作记录。以下提供两种可行的实现方式：

方法一：基于统计匹配的实现 #

通过统计每个成就的总动作数、玩家完成的动作数，筛选出完成数量匹配的玩家-成就组合，再关联动作记录。

完整SQL语句：

SELECT pa.player, aa.achievement, aa.action
FROM Players_Actions pa
JOIN Achievements_Actions aa ON pa.action = aa.action
JOIN (
    -- 筛选玩家已完成全部动作的成就
    SELECT player, achievement
    FROM (
        -- 统计每个玩家对每个成就完成的动作数
        SELECT 
            pa_inner.player, 
            aa_inner.achievement, 
            COUNT(DISTINCT pa_inner.action) AS completed_actions
        FROM Players_Actions pa_inner
        JOIN Achievements_Actions aa_inner ON pa_inner.action = aa_inner.action
        GROUP BY pa_inner.player, aa_inner.achievement
    ) player_progress
    JOIN (
        -- 统计每个成就需要的总动作数
        SELECT achievement, COUNT(action) AS total_actions
        FROM Achievements_Actions
        GROUP BY achievement
    ) achievement_requirements 
    ON player_progress.achievement = achievement_requirements.achievement
    WHERE player_progress.completed_actions = achievement_requirements.total_actions
) valid_achievements 
ON pa.player = valid_achievements.player 
AND aa.achievement = valid_achievements.achievement
ORDER BY pa.player, aa.achievement, aa.action;

逻辑说明 #

1. achievement_requirements：统计每个成就的总动作要求数，比如Adventurer需要2个动作，Legend需要2个动作。
2. player_progress：统计每个玩家针对每个成就已完成的动作数量，比如RandomPlayer对Legend仅完成1个动作。
3. valid_achievements：关联前两个结果，筛选出完成数等于总动作数的玩家-成就组合，即玩家真正解锁的成就。
4. 最终关联：将有效组合与动作表关联，输出对应动作记录。

方法二：基于NOT EXISTS的实现 #

通过双重NOT EXISTS判断：确认某个玩家对某成就，不存在未完成的动作，再关联动作记录。

完整SQL语句：

SELECT pa.player, aa.achievement, aa.action
FROM Players_Actions pa
JOIN Achievements_Actions aa ON pa.action = aa.action
WHERE NOT EXISTS (
    -- 检查该成就是否存在玩家未完成的动作
    SELECT 1
    FROM Achievements_Actions aa_req
    WHERE aa_req.achievement = aa.achievement
    AND NOT EXISTS (
        -- 检查玩家是否完成了该动作
        SELECT 1
        FROM Players_Actions pa_req
        WHERE pa_req.player = pa.player
        AND pa_req.action = aa_req.action
    )
)
ORDER BY pa.player, aa.achievement, aa.action;

逻辑说明 #

这个逻辑更直观：对于当前玩家和成就，遍历该成就的所有动作要求，如果没有任何一个动作是玩家未完成的，则说明玩家解锁了该成就，保留对应的动作记录。

两种方法执行后都会得到你期望的结果：

+--------------+-------------+------------------+
| player       | achievement | action           |
+--------------+-------------+------------------+
| RandomPlayer | Adventurer  | Kill_10_Monsters |
| RandomPlayer | Adventurer  | Kill_1_Boss      |
| RandomPlayer | Warrior     | Kill_10_Monsters |
| RandomPlayer | Warrior     | Kill_5_Bosses    |
+--------------+-------------+------------------+

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