求编写SQL语句筛选参与半数及以上游戏的玩家

嘿，我来帮你搞定这个SQL问题！先理清楚需求和现有代码里的问题：

你需要筛选出参与半数及以上不同游戏类型的玩家——题目里说GameType表固定有6种，也就是要找出参与3种及以上游戏类型的玩家，输出格式要包含玩家ID、姓氏，以及参与的游戏种类数。

你的当前SQL存在几个关键问题，导致无法得到正确结果：

• 分组时加入了dbo.Game.DealerInGame：这会让同一个玩家如果和不同发牌员玩同一种游戏，被拆分成多条记录，统计的游戏数量会出错
• 错误统计总游戏种类数：你在主查询里用count(DISTINCT dbo.Game.gameTypeName)，但因为关联了PlayerInAGame，这个值只能拿到该玩家参与过的游戏类型，不是全局的总GameType数量（也就是你需要的6种）
• 不需要关联Game表：PlayerInAGame已经通过外键关联到Game，且gameTypeName字段直接存储了游戏类型名称，完全可以直接从PlayerInAGame统计，没必要多关联一层Game表

我给你两个方案，优先推荐动态计算的版本，避免硬编码：

方案一：动态计算总游戏类型数（推荐） #

这个方案会自动从GameType表获取总数量，就算后续游戏类型数量变化，SQL也不用修改：

WITH TotalGameTypes AS (
    -- 先统计全局的游戏类型总数
    SELECT COUNT(*) AS total_types
    FROM GameType
),
PlayerGameStats AS (
    -- 统计每个玩家参与的不同游戏类型数量
    SELECT 
        p.ID,
        p.surName,
        COUNT(DISTINCT pig.gameTypeName) AS [number of games played]
    FROM Player p
    INNER JOIN PlayerInAGame pig 
        ON p.ID = pig.playerID
    GROUP BY p.ID, p.surName
)
-- 筛选出参与数量≥半数的玩家
SELECT 
    ID,
    surName,
    [number of games played]
FROM PlayerGameStats
CROSS JOIN TotalGameTypes
WHERE [number of games played] >= CEILING(total_types / 2.0)
ORDER BY ID;

方案二：硬编码游戏数量（适合固定6种的场景） #

如果确定GameType永远是6种，也可以用更简洁的写法：

SELECT 
    p.ID,
    p.surName,
    COUNT(DISTINCT pig.gameTypeName) AS [number of games played]
FROM Player p
INNER JOIN PlayerInAGame pig 
    ON p.ID = pig.playerID
GROUP BY p.ID, p.surName
-- 直接筛选参与3种及以上的玩家
HAVING COUNT(DISTINCT pig.gameTypeName) >= 3
ORDER BY ID;

• CTE TotalGameTypes：单独统计GameType的总数，保证后续如果游戏类型增加/减少，SQL无需改动
• CTE PlayerGameStats：只关联Player和PlayerInAGame，统计每个玩家的独特游戏类型参与数，用DISTINCT确保同一种游戏类型不会被重复统计
• 筛选逻辑：动态方案里用CEILING(total_types / 2.0)来计算半数（比如6种的话就是3，7种的话就是4），硬编码方案直接用3，都能满足需求

运行后会得到和你要求一致的输出格式：

ID   surName  number of games played
1    test1    3
2    test2    4
3    test3    3
4    test4    6

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