如何重构多表数据：课程及组件成绩动态报表生成咨询

现有数据表结构 #

课程表 tblCourses #

CREATE TABLE tblCourses (
    CourseID INT IDENTITY PRIMARY KEY,
    CourseName VARCHAR(300)
)

课程组件表 tblCourseComponents #

CREATE TABLE tblCourseComponents (
    ExamID INT IDENTITY PRIMARY KEY,
    ComponentName VARCHAR(300),
    CourseID INT FOREIGN KEY REFERENCES tblCourses.CourseID
)

学生课程成绩表 tblStudentScoreHistory #

CREATE TABLE tblStudentScoreHistory (
    ScoreID INT IDENTITY PRIMARY KEY,
    CID VARCHAR(8), -- 学生编号
    CourseID INT FOREIGN KEY REFERENCES tblCourses.CourseID,
    UnitMark DECIMAL (8,6), -- 课程原始分
    ModeratedScore DECIMAL (8,6) -- 课程调整分
)

学生组件成绩表 tblStudentComponentInformation #

CREATE TABLE tblStudentComponentInformation (
    ID INT IDENTITY PRIMARY KEY,
    RelatedScoreID INT FOREIGN KEY REFERENCES tblStudentScoreHistory,
    ExamID INT FOREIGN KEY REFERENCES tblCourseComponents(ExamID), -- 修正原笔误lngExamID
    CID VARCHAR(8), -- 学生编号
    ComponentScorePercent DECIMAL(8,6), -- 组件原始分（百分比）
    ModeratedComponentScorePercent DECIMAL(8,6) -- 组件调整分（百分比）
)

数据示例 #

插入课程数据 #

INSERT INTO tblCourses (CourseName)
VALUES ('Course A'), ('Course B'), ('Course C')

插入课程组件数据 #

INSERT INTO tblCourseComponents (ComponentName, CourseID)
VALUES ('Course A Exam 1',1),
('Course A Exam 2',1),
('Course B Exam 1',2),
('Course C Exam 1',3),
('Course C Exam 2',3),
('Course C Coursework 1',3)

需求说明 #

输入指定CourseID后，生成报表需包含：

• 每个课程组件的原始分和调整分（组件数量动态对应列数，当前最多4个，后续可能调整）
• 该课程对应的课程原始分和课程调整分

解决方案 #

方案1：静态CASE表达式（适合已知最大组件数场景） #

如果当前组件数最多4个，可直接用CASE表达式手动定义列，适合不需要频繁调整的场景：

DECLARE @TargetCourseID INT = 3; -- 输入目标课程ID

SELECT
    s.CID,
    MAX(CASE WHEN comp_seq = 1 THEN sci.ComponentScorePercent END) AS [组件1_原始分],
    MAX(CASE WHEN comp_seq = 1 THEN sci.ModeratedComponentScorePercent END) AS [组件1_调整分],
    MAX(CASE WHEN comp_seq = 2 THEN sci.ComponentScorePercent END) AS [组件2_原始分],
    MAX(CASE WHEN comp_seq = 2 THEN sci.ModeratedComponentScorePercent END) AS [组件2_调整分],
    MAX(CASE WHEN comp_seq = 3 THEN sci.ComponentScorePercent END) AS [组件3_原始分],
    MAX(CASE WHEN comp_seq = 3 THEN sci.ModeratedComponentScorePercent END) AS [组件3_调整分],
    MAX(CASE WHEN comp_seq = 4 THEN sci.ComponentScorePercent END) AS [组件4_原始分],
    MAX(CASE WHEN comp_seq = 4 THEN sci.ModeratedComponentScorePercent END) AS [组件4_调整分],
    s.UnitMark AS [课程原始分],
    s.ModeratedScore AS [课程调整分]
FROM tblStudentScoreHistory s
LEFT JOIN (
    -- 给目标课程的组件分配序号
    SELECT 
        ExamID, 
        CourseID,
        ROW_NUMBER() OVER(PARTITION BY CourseID ORDER BY ExamID) AS comp_seq
    FROM tblCourseComponents
    WHERE CourseID = @TargetCourseID
) cc ON s.CourseID = cc.CourseID
LEFT JOIN tblStudentComponentInformation sci 
    ON s.ScoreID = sci.RelatedScoreID 
    AND cc.ExamID = sci.ExamID
WHERE s.CourseID = @TargetCourseID
GROUP BY s.CID, s.UnitMark, s.ModeratedScore
ORDER BY s.CID;

方案2：动态SQL+PIVOT（适合组件数动态调整场景） #

如果后续组件数量可能变化，用动态SQL自动生成列，无需手动修改代码：

DECLARE @TargetCourseID INT = 3; -- 输入目标课程ID
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 自动生成组件对应的原始分、调整分列名
WITH CourseComponents AS (
    SELECT 
        ComponentName,
        ROW_NUMBER() OVER(PARTITION BY CourseID ORDER BY ExamID) AS comp_seq
    FROM tblCourseComponents
    WHERE CourseID = @TargetCourseID
)
SELECT @PivotColumns = STRING_AGG(
    QUOTENAME(CONCAT('组件', comp_seq, '_', ComponentName, '_', [类型])), ', '
)
FROM CourseComponents
CROSS JOIN (VALUES ('原始分'), ('调整分')) AS Types([类型]);

-- 构建动态查询语句
SET @SQL = N'
SELECT 
    CID,
    ' + @PivotColumns + ',
    UnitMark AS [课程原始分],
    ModeratedScore AS [课程调整分]
FROM (
    SELECT 
        s.CID,
        CONCAT(''组件'', cc.comp_seq, ''_'', cc.ComponentName, ''_'', [类型]) AS ColName,
        CASE [类型]
            WHEN ''原始分'' THEN sci.ComponentScorePercent
            WHEN ''调整分'' THEN sci.ModeratedComponentScorePercent
        END AS ScoreValue,
        s.UnitMark,
        s.ModeratedScore
    FROM tblStudentScoreHistory s
    LEFT JOIN (
        SELECT 
            ExamID, 
            CourseID,
            ComponentName,
            ROW_NUMBER() OVER(PARTITION BY CourseID ORDER BY ExamID) AS comp_seq
        FROM tblCourseComponents
        WHERE CourseID = ' + CAST(@TargetCourseID AS NVARCHAR) + '
    ) cc ON s.CourseID = cc.CourseID
    LEFT JOIN tblStudentComponentInformation sci 
        ON s.ScoreID = sci.RelatedScoreID 
        AND cc.ExamID = sci.ExamID
    CROSS JOIN (VALUES (''原始分''), (''调整分'')) AS Types([类型])
    WHERE s.CourseID = ' + CAST(@TargetCourseID AS NVARCHAR) + '
) src
PIVOT (
    MAX(ScoreValue)
    FOR ColName IN (' + @PivotColumns + ')
) pvt
ORDER BY CID;';

-- 执行动态SQL
EXEC sp_executesql @SQL;

方案说明 #

1. 方案1优势是简单直观，适合组件数量固定的场景，但后续组件数超过预设值时需手动修改代码。
2. 方案2会根据目标课程的实际组件数自动生成对应列，无需手动调整，适配组件数量变化的需求。
3. 修正了原表结构中外键lngExamID的笔误，改为与tblCourseComponents主键匹配的ExamID。

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