SQL Server 2016多表分组查询：按作者ID统计近6月玩家数

嘿，我刚梳理完你的需求，这事儿我之前在项目里碰到过类似的，给你一步步拆解怎么实现：

首先，咱们得把所有关联的表串起来，锁定指定作者的游戏数据，然后按月份统计玩家数，最后转成你要的横向格式。这里要注意几个关键点：确保近6个月的所有月份都能显示（哪怕当月没有玩家）、避免重复统计同一个玩家、按从新到旧的顺序排列月份。

完整SQL查询代码（兼容SQL Server 2016） #

DECLARE @AuthorID INT = 1; -- 替换成你实际要查询的作者ID
DECLARE @EndDate DATE = GETDATE();
DECLARE @StartDate DATE = DATEADD(MONTH, -6, @EndDate);

-- 生成近6个月的月份列表，确保即使没有玩家数据也能显示对应月份
WITH MonthsCTE AS (
    SELECT 
        DATEADD(MONTH, n, DATEFROMPARTS(YEAR(@StartDate), MONTH(@StartDate), 1)) AS MonthStart
    FROM (
        SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
    ) AS Numbers
),
-- 关联所有表，筛选指定作者的玩家数据，去重统计每个月的玩家数
PlayerStats AS (
    SELECT
        FORMAT(m.MonthStart, 'MMMyyyy') AS MonthLabel,
        COUNT(DISTINCT p.ID) AS PlayerCount
    FROM MonthsCTE m
    LEFT JOIN GamesPlayed gp 
        ON gp.DatePlayed >= m.MonthStart 
        AND gp.DatePlayed < DATEADD(MONTH, 1, m.MonthStart)
    LEFT JOIN QuizGames qg ON gp.QuizGameID = qg.ID
    LEFT JOIN Quizzes q ON qg.QuizID = q.ID
    LEFT JOIN Authors a ON q.AuthorID = a.ID AND a.ID = @AuthorID -- 限定目标作者
    LEFT JOIN Players p ON gp.ID = p.GamePlayedID -- 关联玩家表（注意你的外键对应关系）
    GROUP BY m.MonthStart, FORMAT(m.MonthStart, 'MMMyyyy')
)
-- 将行转成横向的字符串格式（SQL Server 2016不支持STRING_AGG，用FOR XML PATH实现）
SELECT 
    -- 拼接月份字符串
    STUFF((
        SELECT ' ' + MonthLabel
        FROM PlayerStats
        ORDER BY MonthStart DESC -- 按从新到旧排序
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Months,
    -- 拼接玩家数字符串
    STUFF((
        SELECT ' ' + CAST(PlayerCount AS NVARCHAR(10))
        FROM PlayerStats
        ORDER BY MonthStart DESC
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS PlayerCounts
FROM PlayerStats
GROUP BY (SELECT NULL);

关键部分解释 #

1. 生成月份列表：用MonthsCTE生成近6个月的第一天，这样哪怕某个月没有该作者的游戏记录，也能保留这个月份的条目，避免结果缺失。
2. 表关联与筛选：
   • 用LEFT JOIN串联所有表，确保月份不会被过滤掉；
   • 把作者ID的条件放在Authors表的JOIN里，而不是WHERE子句，这样不会丢失没有数据的月份；
   • COUNT(DISTINCT p.ID)保证同一个玩家在同一个月内只被统计一次（如果你的需求是统计游戏次数而非玩家数，去掉DISTINCT即可）。
3. 日期范围匹配：用gp.DatePlayed >= m.MonthStart AND gp.DatePlayed < DATEADD(MONTH, 1, m.MonthStart)精准匹配每个月的记录，避免跨月统计错误。
4. 横向格式拼接：因为SQL Server 2016不支持STRING_AGG函数，所以用FOR XML PATH的方式把月份和玩家数拼接成横向的字符串，刚好符合你要的输出格式。

输出示例 #

运行后会返回类似这样的结果：

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