SQL Server CAST列OrderBy实现 解决团队参赛积分月度统计报错
问题根因
- 原查询直接关联全量
PlayerActivity表,因为单玩家会有多条参赛活动记录,关联后必然产生多行重复数据;为了去重使用了DISTINCT,此时如果追加ORDER BY PA.[ActivityDate] DESC排序,由于该字段没有出现在SELECT选择列表中,就会触发SQL Server的语法报错。 - 原查询的输出粒度是单个玩家,没有按年月维度做团队级的积分聚合,和“按月统计团队总参赛积分”的业务目标不匹配。
- 原逻辑没有遵循特殊计分规则:没有将单玩家的全部积分归集到其最后一次参赛的年月,而是直接关联每次参赛记录取月份,会把积分拆分到不同月份,完全不符合计分要求。
实现思路
- 先从
PlayerActivity表中筛选参赛类型的活动记录,按PlayerId分组取最大的ActivityDate,拿到每个玩家最后一次参赛的日期,从根源上避免关联全量活动记录产生的多行问题。 - 关联团队-玩家映射表、玩家表,基于已拿到的最后参赛日期提取对应年月,按年月分组累加团队下所有玩家的积分——积分计算严格遵循给定规则:
ISNULL(P.[TotalMatchesPlayed], 0) * 5,不需要从活动记录中计数参赛场次。 - 匹配预期输出要求:月份输出为两位补零的格式(如3月显示为
03),年份为四位数字;为了覆盖连续月份的展示需求(无积分的月份也要输出,值为0),生成统计时间范围内的连续月度列表,通过左连接匹配对应月份的聚合积分,无匹配值的月份默认填充0。 - 排序字段直接引用SELECT列表中的年、月字段,完全符合SQL Server的语法要求,彻底规避之前的DISTINCT排序报错问题。
参考实现代码
WITH PlayerLastMatch AS ( -- 提取每个玩家最后一次参赛的日期 SELECT PlayerId, MAX(ActivityDate) AS LastMatchDate FROM PlayerActivity WHERE ActivityTypeId = 14 -- 匹配参赛类型的活动记录 GROUP BY PlayerId ), TeamMatchScope AS ( -- 确定当前团队的月度统计范围(从最早参赛月到最晚参赛月) SELECT MIN(DATEFROMPARTS(YEAR(PLM.LastMatchDate), MONTH(PLM.LastMatchDate), 1)) AS RangeStart, MAX(DATEFROMPARTS(YEAR(PLM.LastMatchDate), MONTH(PLM.LastMatchDate), 1)) AS RangeEnd FROM PlayerLastMatch PLM INNER JOIN TeamPlayers TP ON PLM.PlayerId = TP.PlayerId WHERE TP.TeamId = @TeamId -- 替换为存储过程传入的实际团队ID ), ContinuousMonths AS ( -- 递归生成范围内的所有连续月份 SELECT RangeStart AS MonthStart, RangeEnd FROM TeamMatchScope UNION ALL SELECT DATEADD(MONTH, 1, MonthStart), RangeEnd FROM ContinuousMonths WHERE MonthStart < RangeEnd ), MonthlyAggregatedPoints AS ( -- 按月份聚合团队总积分 SELECT DATEFROMPARTS(YEAR(PLM.LastMatchDate), MONTH(PLM.LastMatchDate), 1) AS MonthStart, SUM(ISNULL(P.[TotalMatchesPlayed], 0) * 5) AS ParticipationPoints FROM TeamPlayers TP INNER JOIN Player P ON TP.PlayerId = P.PlayerId INNER JOIN PlayerLastMatch PLM ON P.PlayerId = PLM.PlayerId WHERE TP.TeamId = @TeamId GROUP BY DATEFROMPARTS(YEAR(PLM.LastMatchDate), MONTH(PLM.LastMatchDate), 1) ) SELECT RIGHT('0' + CAST(MONTH(CM.MonthStart) AS VARCHAR(2)), 2) AS [Month], CAST(YEAR(CM.MonthStart) AS VARCHAR(4)) AS [Year], ISNULL(MAP.ParticipationPoints, 0) AS ParticipationPoints FROM ContinuousMonths CM LEFT JOIN MonthlyAggregatedPoints MAP ON CM.MonthStart = MAP.MonthStart ORDER BY [Year], [Month] OPTION (MAXRECURSION 1000); -- 支撑最多1000个月的递归生成,满足长期统计需求
规则匹配说明
- 完全遵循特殊计分规则:单玩家的全部参赛积分仅归集到其最后一次参赛对应的年月,不会拆分到其他参赛月份,和题目给出的示例计算逻辑完全一致。
- 彻底解决原查询的重复行问题:通过提前聚合每个玩家的最后参赛日期,避免关联全量活动记录带来的多行匹配。
- 彻底解决排序报错问题:不再使用
DISTINCT做去重,排序字段全部来自SELECT输出列,符合SQL Server的语法规范。 - 输出格式完全匹配要求:两位补零的月份、四位年份,无积分的月份自动填充0值,和示例输出格式一致。
内容的提问来源于stack exchange,提问作者adityaa
相关产品推荐
相关产品推荐

