如何用GROUP BY验证满双射关系及两类SQL查询实现求助

首先先明确一下我假设的表结构（如果你的字段名不同，替换成对应名称即可）：

• players: id（玩家ID）、name（玩家名称）等基础字段
• missions: id（任务ID）、name（任务名称）等基础字段
• mission_assignment: player_id（关联玩家ID）、mission_id（关联任务ID）——存储玩家与任务的分配关系
• completion_record: player_id、mission_id、completed_at（完成时间）——存储玩家完成任务的记录

1. 查询完成所有已分配任务的玩家 #

方法一：使用GROUP BY + HAVING统计数量匹配 #

这个思路是统计每个玩家的已分配任务总数和已完成任务数，当两者相等时，说明该玩家完成了所有分配的任务：

SELECT p.id, p.name
FROM players p
JOIN mission_assignment ma ON p.id = ma.player_id
LEFT JOIN completion_record cr 
  ON ma.player_id = cr.player_id 
  AND ma.mission_id = cr.mission_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT ma.mission_id) = COUNT(DISTINCT cr.mission_id)

• LEFT JOIN确保我们保留所有已分配的任务，未完成的任务在completion_record中会返回NULL，而COUNT会忽略NULL值
• DISTINCT是为了避免重复记录（比如同一个任务被多次分配或多次完成的极端情况）

方法二：使用NOT EXISTS排除未完成任务的玩家 #

这个逻辑更直观：找到不存在任何已分配但未完成任务的玩家：

SELECT p.id, p.name
FROM players p
WHERE NOT EXISTS (
    -- 查找该玩家已分配但未完成的任务
    SELECT 1
    FROM mission_assignment ma
    WHERE ma.player_id = p.id
    AND NOT EXISTS (
        SELECT 1
        FROM completion_record cr
        WHERE cr.player_id = ma.player_id
        AND cr.mission_id = ma.mission_id
    )
)

2. 查询未完成所有已分配任务的玩家 #

这其实是上面问题的反向查询，同样有两种实现方式：

方法一：GROUP BY + HAVING统计数量不匹配 #

SELECT p.id, p.name
FROM players p
JOIN mission_assignment ma ON p.id = ma.player_id
LEFT JOIN completion_record cr 
  ON ma.player_id = cr.player_id 
  AND ma.mission_id = cr.mission_id
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT ma.mission_id) > COUNT(DISTINCT cr.mission_id)

方法二：使用EXISTS找到存在未完成任务的玩家 #

SELECT p.id, p.name
FROM players p
WHERE EXISTS (
    -- 找到该玩家至少一个已分配但未完成的任务
    SELECT 1
    FROM mission_assignment ma
    WHERE ma.player_id = p.id
    AND NOT EXISTS (
        SELECT 1
        FROM completion_record cr
        WHERE cr.player_id = ma.player_id
        AND cr.mission_id = ma.mission_id
    )
)

用GROUP BY验证满双射（一一对应）关系 #

这里的「满双射」应该是指玩家的已分配任务集合与已完成任务集合完全相等——既没有未完成的分配任务，也没有完成了未被分配的任务。我们可以通过GROUP BY结合条件判断来验证：

SELECT p.id, p.name
FROM players p
LEFT JOIN mission_assignment ma ON p.id = ma.player_id
LEFT JOIN completion_record cr ON p.id = cr.player_id
GROUP BY p.id, p.name
HAVING 
    -- 分配任务数 = 完成任务数
    COUNT(DISTINCT ma.mission_id) = COUNT(DISTINCT cr.mission_id)
    -- 没有完成任何未被分配的任务
    AND COUNT(DISTINCT 
        CASE 
            WHEN cr.mission_id NOT IN (SELECT mission_id FROM mission_assignment WHERE player_id = p.id) 
            THEN cr.mission_id 
        END
    ) = 0

或者用更清晰的集合差集方式（部分数据库支持EXCEPT语法，比如PostgreSQL、SQL Server等）：

SELECT p.id, p.name
FROM players p
WHERE 
    -- 分配的任务全部完成
    NOT EXISTS (
        SELECT mission_id FROM mission_assignment WHERE player_id = p.id
        EXCEPT
        SELECT mission_id FROM completion_record WHERE player_id = p.id
    )
    -- 完成的任务全部是已分配的
    AND NOT EXISTS (
        SELECT mission_id FROM completion_record WHERE player_id = p.id
        EXCEPT
        SELECT mission_id FROM mission_assignment WHERE player_id = p.id
    )

这个查询会返回那些分配任务和完成任务完全一一对应的玩家，完美验证满双射关系。

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