在SSMS中如何将3次SQL查询的结果合并展示在同一张表中

你当前多次返回独立结果的原因是：WHILE循环内每次执行SELECT都会生成独立结果集，末尾的CGPA查询又单独生成一个结果集，要合并为同一张表可以参考以下两种方案：

方案1：更高效的无循环实现（推荐） #

无需多次循环查表，直接通过GROUP BY semid一次性查询所有学期的成绩，再通过UNION ALL拼接汇总的CGPA行即可，同时修正了你原有代码中过滤条件的逻辑错误：

注意：你原有代码里的过滤条件(grade != 'F' OR grade != 'Ab' OR grade != 'IN')存在逻辑错误，该条件永远为真，无法过滤掉不及格/缺考/缓考的成绩，需要改为AND连接或者用NOT IN写法才符合预期。

-- 查询各学期成绩
SELECT
    SUM(sb.totalmarks) AS Sub_total,
    SUM(sb.credit) AS Credit_total, 
    SUM(a.totalmarks) AS Total_obt, 
    SUM(a.rankpoint) AS RP_total,
    (SUM(a.rankpoint) / SUM(sb.credit)) AS SGPA,
    CAST(a.semid AS VARCHAR(10)) AS Sem,
    NULL AS TotalCredit,
    NULL AS TotalGradepoint,
    NULL AS CGPA
FROM
    umms.dbo.[dt_result] AS a 
JOIN
    umms.dbo.[dt_subjectdetails] AS sb ON a.paperid = sb.paperid  
WHERE
    studentid = 17780 
    AND btid = 495 
    AND a.semid <= @stsem -- 对应你原有循环的终止条件
    AND grade NOT IN ('F','Ab','IN') -- 修正原有OR逻辑错误
GROUP BY a.semid

UNION ALL

-- 拼接全学期汇总CGPA行
SELECT
    NULL AS Sub_total,
    NULL AS Credit_total, 
    NULL AS Total_obt, 
    NULL AS RP_total,
    NULL AS SGPA,
    '汇总' AS Sem,
    SUM(b.credit) as TotalCredit,
    SUM(a.rankpoint) as TotalGradepoint,
    (SUM(a.rankpoint)/SUM(b.credit)) as CGPA 
FROM umms.dbo.[dt_result] as a 
JOIN umms.dbo.[dt_subjectdetails] as b on a.paperid=b.paperid 
WHERE studentid=17780 and btid=495

返回结果中前几行是各学期的成绩数据，最后一行是全学期汇总的CGPA数据，全部在同一张表内。

方案2：保留原有循环逻辑的修改方法 #

如果必须保留循环写法，可以先创建临时表存储每次循环的查询结果，最后再拼接汇总行：

-- 创建临时表存储各学期数据
CREATE TABLE #temp_score (
    Sub_total INT,
    Credit_total INT,
    Total_obt INT,
    RP_total DECIMAL(10,2),
    SGPA DECIMAL(10,2),
    Sem VARCHAR(10),
    TotalCredit INT,
    TotalGradepoint DECIMAL(10,2),
    CGPA DECIMAL(10,2)
)

WHILE @i <= @stsem
BEGIN
    INSERT INTO #temp_score
    SELECT
        SUM(sb.totalmarks) AS Sub_total,
        SUM(sb.credit) AS Credit_total, 
        SUM(a.totalmarks) AS Total_obt, 
        SUM(a.rankpoint) AS RP_total,
        (SUM(rankpoint) / SUM(sb.credit)) AS SGPA,
        CAST(@i AS VARCHAR(10)) AS Sem,
        NULL AS TotalCredit,
        NULL AS TotalGradepoint,
        NULL AS CGPA
    FROM
        umms.dbo.[dt_result] AS a 
    JOIN
        umms.dbo.[dt_subjectdetails] AS sb ON a.paperid = sb.paperid  
    WHERE
        studentid = 17780 
        AND btid = 495 
        AND a.semid = @i  
        AND grade NOT IN ('F','Ab','IN') -- 修正原有逻辑错误
              
    IF @i = 10
        BREAK; 

    SET @i = @i + 1;
END;

-- 插入汇总行
INSERT INTO #temp_score
SELECT
    NULL AS Sub_total,
    NULL AS Credit_total, 
    NULL AS Total_obt, 
    NULL AS RP_total,
    NULL AS SGPA,
    '汇总' AS Sem,
    SUM(b.credit) as TotalCredit,
    SUM(rankpoint) as TotalGradepoint ,
    (SUM(rankpoint)/SUM(b.credit)) as CGPA 
FROM umms.dbo.[dt_result] as a 
JOIN umms.dbo.[dt_subjectdetails] as b on a.paperid=b.paperid 
WHERE studentid=17780 and btid=495

-- 统一查询返回所有结果
SELECT * FROM #temp_score
-- 用完删除临时表
DROP TABLE #temp_score

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