如何通过SQL实现单用户测验结果的证书式格式化输出？

针对你想要生成类似证书格式的单用户测验结果需求，我整理了几个实用的SQL方案，其中直接用UNION ALL结合排序的方式最直观高效，CTE和STUFF也能配合实现，下面详细说明：

首先，你的数据库结构和示例数据（补充了查询用到的quiz表）如下：

CREATE TABLE [dbo].[users] ( 
    [user_id] [bigint] IDENTITY(1,1) NOT NULL, 
    [user_name] [nvarchar](50) NULL, 
    [first_name] [nvarchar](50) NULL, 
    [last_name] [nvarchar](50) NULL, 
    [id_number] [nvarchar](50) NULL, 
    CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [user_id] ASC ) 
) 

insert into users (user_name, first_name, last_name, id_number) 
select 'user1','John','Brown','7707071231' union all 
select 'user2','Mary','Jane','7303034432' union all 
select 'user3','Peter','Pan','5503024441' 

CREATE TABLE [dbo].[quiz_results] ( 
    [result_id] [bigint] IDENTITY(1,1) NOT NULL, 
    [quiz_id] [bigint] NOT NULL, 
    [user_id] [bigint] NOT NULL, 
    [grade] [bigint] NULL, 
    CONSTRAINT [PK_quizresults] PRIMARY KEY CLUSTERED ( [result_id] ASC ) 
) 

insert into quiz_results (quiz_id, user_id, grade) 
select 1,1,88 union all 
select 2,1,84 union all 
select 3,1,33 union all 
select 1,2,65 

-- 补充查询依赖的quiz表
CREATE TABLE [dbo].[quiz] (
    [quiz_id] [bigint] NOT NULL,
    [quiz_name] [nvarchar](50) NULL,
    CONSTRAINT [PK_quiz] PRIMARY KEY CLUSTERED ([quiz_id] ASC)
)

insert into quiz (quiz_id, quiz_name)
select 1, 'quiz a' union all
select 2, 'quiz b' union all
select 3, 'quiz c'

你当前的查询会重复显示student_name在每行，而期望首行仅展示用户信息，后续行只显示测验名称和成绩，类似证书的格式。

方案1：UNION ALL + 排序（推荐，直观高效） #

这个方案直接将用户信息行和测验结果行合并，通过排序保证用户行在最前面，再用CASE控制每行显示的内容，完全在SQL中生成你想要的格式：

基础版（分列显示） #

SELECT 
    -- 仅第一行显示用户名称，其余行留空
    CASE WHEN rn = 0 THEN student_name ELSE '' END AS student_name,
    -- 第一行测验名称留空，其余行显示测验名
    CASE WHEN rn = 0 THEN '' ELSE quiz_name END AS quiz_name,
    -- 第一行成绩留空，其余行显示成绩
    CASE WHEN rn = 0 THEN '' ELSE CAST(grade AS VARCHAR(10)) END AS grade
FROM (
    -- 首行：用户信息
    SELECT 
        users.first_name + ' ' + users.last_name + ' (' + users.id_number + ')' AS student_name,
        '' AS quiz_name,
        '' AS grade,
        0 AS rn  -- 标记为第一行
    FROM users
    WHERE users.user_id = 1  -- 替换为目标user_id
    UNION ALL
    -- 后续行：测验结果
    SELECT 
        '' AS student_name,
        quiz.quiz_name,
        CAST(quiz_results.grade AS VARCHAR(10)) AS grade,
        -- 用行号标记后续行，保证排序正确
        ROW_NUMBER() OVER (ORDER BY quiz_results.quiz_id) AS rn
    FROM quiz_results
    INNER JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
    WHERE quiz_results.user_id = 1  -- 替换为目标user_id
) AS combined_data
ORDER BY rn;

带分隔线的证书格式版（完全匹配示例） #

如果要生成和你给出的示例完全一致的带分隔线的文本格式，可以直接拼接字符串：

SELECT display_text AS certificate_line
FROM (
    -- 顶部分隔线
    SELECT '+-------------------------+' AS display_text, 0 AS rn
    UNION ALL
    -- 用户信息行
    SELECT '| ' + users.first_name + ' ' + users.last_name + ' (' + users.id_number + ') |' AS display_text, 1 AS rn
    FROM users
    WHERE users.user_id = 1  -- 替换为目标user_id
    UNION ALL
    -- 中间分隔线
    SELECT '+-------------------------+' AS display_text, 2 AS rn
    UNION ALL
    -- 测验结果行
    SELECT '| ' + quiz.quiz_name + ' | ' + CAST(quiz_results.grade AS VARCHAR(10)) + ' |' AS display_text, 
           2 + ROW_NUMBER() OVER (ORDER BY quiz_results.quiz_id) AS rn
    FROM quiz_results
    INNER JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
    WHERE quiz_results.user_id = 1  -- 替换为目标user_id
    UNION ALL
    -- 底部分隔线
    SELECT '+-------------------------+' AS display_text, 999 AS rn
) AS certificate_data
ORDER BY rn;

这个查询的输出和你给出的示例完全一致，每行对应证书的一行，不需要客户端额外处理。

方案2：CTE（代码结构优化） #

CTE和上面的子查询逻辑完全一致，只是把合并的数据集用CTE定义，让代码结构更清晰，适合逻辑更复杂的场景：

WITH certificate_data AS (
    -- 首行：用户信息
    SELECT 
        users.first_name + ' ' + users.last_name + ' (' + users.id_number + ')' AS student_name,
        '' AS quiz_name,
        '' AS grade,
        0 AS rn
    FROM users
    WHERE users.user_id = 1
    UNION ALL
    -- 后续行：测验结果
    SELECT 
        '',
        quiz.quiz_name,
        CAST(quiz_results.grade AS VARCHAR(10)),
        ROW_NUMBER() OVER (ORDER BY quiz_results.quiz_id) AS rn
    FROM quiz_results
    JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
    WHERE quiz_results.user_id = 1
)
SELECT 
    CASE WHEN rn = 0 THEN student_name ELSE '' END,
    CASE WHEN rn = 0 THEN '' ELSE quiz_name END,
    CASE WHEN rn = 0 THEN '' ELSE grade END
FROM certificate_data
ORDER BY rn;

方案3：STUFF + XML PATH（生成单字符串证书） #

如果需要将整个证书作为单个字符串输出（比如导出为文本文件），可以用STUFF配合XML PATH拼接所有行：

SELECT 
    '+-------------------------+' + CHAR(13)+CHAR(10) +
    '| ' + users.first_name + ' ' + users.last_name + ' (' + users.id_number + ') |' + CHAR(13)+CHAR(10) +
    '+-------------------------+' + CHAR(13)+CHAR(10) +
    -- 拼接所有测验结果行
    STUFF((
        SELECT CHAR(13)+CHAR(10) + '| ' + quiz.quiz_name + ' | ' + CAST(quiz_results.grade AS VARCHAR(10)) + ' |'
        FROM quiz_results
        JOIN quiz ON quiz_results.quiz_id = quiz.quiz_id
        WHERE quiz_results.user_id = users.user_id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 0, '') +
    CHAR(13)+CHAR(10) + '+-------------------------+' AS full_certificate
FROM users
WHERE users.user_id = 1;

这个查询会返回一个包含所有证书内容的字符串，每行用换行符分隔。

• UNION ALL方案：最优选择，逻辑简单，性能优秀，直接生成多行结果，完全匹配你的需求。
• CTE方案：和UNION ALL性能一致，只是代码结构更清晰，适合复杂场景的逻辑拆分。
• STUFF方案：适合需要单个字符串输出的场景，比如导出文本，但如果需要多行结果则不如UNION ALL直接。

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