SQL Server动态PIVOT按学年统计各课程注册学生数实现咨询
SQL Server 按学年统计课程注册人数 PIVOT 实现方案
PIVOT 完全适配该统计场景,现有代码的问题是字段映射、聚合逻辑、动态列对应关系写错,并非功能不匹配。
现有代码的核心错误
- PIVOT 字段绑定错位:需求是将学年(
SessionName)转为列,却错误绑定了StudentName,和动态拼接的列名完全不匹配 - 聚合逻辑错误:统计注册人数应使用计数逻辑,原代码用
SUM(AdmissionID)求和学生ID,没有实际统计意义 - 动态列映射混乱:拼接的
@cols是学年列名,却被用在绑定StudentName的 PIVOT 逻辑中 - 中间结果集冗余:PIVOT 会自动按未被聚合、未绑定到
FOR的字段分组,多余的CourseID、StudentName字段会导致结果拆分不符合预期 - 未实现合计逻辑:缺少需求要求的 Total 总人数列
修正后的可直接运行的代码
DECLARE @cols NVARCHAR(MAX), -- 用于PIVOT的学年列名清单 @colsSelect NVARCHAR(MAX), -- 用于SELECT的列(空值转0) @totalCol NVARCHAR(MAX), -- 总注册人数合计列 @query NVARCHAR(MAX) -- 拼接需要转列的学年名,按学年倒序排列避免列顺序混乱 SELECT @cols = STUFF(( SELECT ',' + QUOTENAME(SessionName) FROM SIS_tbl_AdmissionSession ORDER BY SessionName DESC -- 可根据需要调整排序规则,也可加WHERE过滤指定学年范围 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), -- 拼接SELECT用的展示列,无数据的学年自动显示0 @colsSelect = STUFF(( SELECT ', ISNULL(' + QUOTENAME(SessionName) + ', 0) AS ' + QUOTENAME(SessionName) FROM SIS_tbl_AdmissionSession ORDER BY SessionName DESC FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') -- 拼接合计列:自动计算每个课程所有学年的总注册人数 SELECT @totalCol = ' , ' + STUFF(( SELECT ' + ISNULL(' + QUOTENAME(SessionName) + ', 0)' FROM SIS_tbl_AdmissionSession ORDER BY SessionName DESC FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 3, '') + ' AS Total' -- 组装最终动态PIVOT查询 SET @query = N' SELECT ProgramName AS [Course Name]' + @colsSelect + @totalCol + N' FROM ( -- 中间结果集只保留统计必需的字段,避免多余字段干扰PIVOT分组 SELECT a.AdmissionID, b.ProgramName, c.SessionName FROM SIS_tbl_AdmissionForm a INNER JOIN SIS_tbl_Programs b ON a.CourseID = b.ProgramId INNER JOIN SIS_tbl_AdmissionSession c ON a.AdmSessionID = c.AdmSessionID ) AS StudentRegistration PIVOT ( COUNT(AdmissionID) -- 按课程+学年分组统计注册人数 FOR SessionName IN (' + @cols + N') ) AS PivotTable ORDER BY ProgramName ' EXEC sp_executesql @query
效果说明
- 自动适配学年列:自动读取数据库中所有存在的学年作为列,无需手动维护列名
- 无注册记录的学年自动显示0
- 自动生成每个课程的总注册人数列
- 输出格式完全匹配预期的统计表结构
内容的提问来源于stack exchange,提问作者for lrbt
相关产品推荐
相关产品推荐

