You need to enable JavaScript to run this app. SQL Server动态PIVOT按学年统计各课程注册学生数实现咨询
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.12 02:15:05