如何用T-SQL实现带对齐格式的表格打印与邮件内容生成

一、生成对齐的文本表格（无需循环） #

要实现列对齐且避免冗余代码，可通过计算每列的最大长度，动态补充空格来统一列宽，同时用集合操作替代低效的WHILE循环：

DECLARE @TableText NVARCHAR(MAX) = '';

-- 1. 封装数据与列长度计算逻辑
WITH SolutionData AS (
    SELECT 
        s.[Name] AS SolutionName,
        -- 模拟目标格式的StatusID后缀（根据实际需求调整）
        CAST(s.[StatusID] AS NVARCHAR(10)) + 'b' AS StatusID,
        -- 格式化日期为目标样式
        FORMAT(n.[ExpirationDate], 'd', 'en-US') AS ExpirationDate
    FROM [dbo].[Solution] AS s
    INNER JOIN [dbo].[Notifications] AS n
        ON s.[ID] = n.SolutionID
),
ColumnLengths AS (
    SELECT
        MAX(LEN(SolutionName)) AS MaxSolLen,
        MAX(LEN(StatusID)) AS MaxStatusLen,
        MAX(LEN(ExpirationDate)) AS MaxExpLen
    FROM SolutionData
)
-- 2. 拼接表头、分隔线与内容行
SELECT @TableText += 
    CASE WHEN @TableText = '' THEN '' ELSE CHAR(13) + CHAR(10) END +
    LineText
FROM (
    -- 生成对齐的表头
    SELECT 
        'Solution' + REPLICATE(' ', MaxSolLen - LEN('Solution')) + ' | Status ID' + REPLICATE(' ', MaxStatusLen - LEN('Status ID')) + ' | Expiration' AS LineText
    FROM ColumnLengths
    UNION ALL
    -- 生成分隔线
    SELECT REPLICATE('-', MaxSolLen + MaxStatusLen + MaxExpLen + 7) AS LineText
    FROM ColumnLengths
    UNION ALL
    -- 生成对齐的内容行
    SELECT 
        SolutionName + REPLICATE(' ', (SELECT MaxSolLen FROM ColumnLengths) - LEN(SolutionName)) + ' | ' + 
        REPLICATE(' ', (SELECT MaxStatusLen FROM ColumnLengths) - LEN(StatusID)) + StatusID + ' | ' + 
        ExpirationDate AS LineText
    FROM SolutionData
) AS Lines;

-- 打印最终对齐的表格
PRINT @TableText;

逻辑说明 #

• 用CTESolutionData统一处理原始数据，包括格式转换（如StatusID后缀、日期格式化）
• ColumnLengths自动计算每列的最大字符长度，后续新增列只需添加对应MAX(LEN(列名))即可
• 通过REPLICATE(' ', 最大长度 - 当前值长度)自动补空格，实现列对齐，无需手动硬编码长度

二、替换低效的WHILE循环 #

原代码中的WHILE循环逐行处理数据，不仅效率低，还容易出错。上述方案用集合操作一次性生成所有行的格式化文本，更符合SQL的集合思维，代码简洁且易维护。

三、数据库邮件发送方案 #

1. 配置Database Mail #

首先在SSMS中完成基础配置：

• 启用Database Mail：

  sp_configure 'show advanced options', 1;
  RECONFIGURE;
  sp_configure 'Database Mail XPs', 1;
  RECONFIGURE;
  

• 打开【管理】->【Database Mail】，通过向导创建邮件配置文件，设置SMTP服务器、发件人信息

2. 生成HTML格式邮件（更美观） #

如果需要给用户发送更规整的邮件，可生成HTML表格替代纯文本：

DECLARE @HtmlBody NVARCHAR(MAX);
DECLARE @RecipientEmail NVARCHAR(100) = 'user@example.com'; -- 替换为用户专属邮箱

WITH UserSolutionData AS (
    SELECT 
        s.[Name] AS SolutionName,
        CAST(s.[StatusID] AS NVARCHAR(10)) + 'b' AS StatusID,
        FORMAT(n.[ExpirationDate], 'd', 'en-US') AS ExpirationDate
    FROM [dbo].[Solution] AS s
    INNER JOIN [dbo].[Notifications] AS n
        ON s.[ID] = n.SolutionID
    WHERE s.UserEmail = @RecipientEmail -- 筛选当前用户的专属数据
      AND n.ExpirationDate <= DATEADD(day, 7, GETDATE()) -- 按过期日期触发
)
SELECT @HtmlBody = 
    N'<html><body><h3>你的解决方案即将过期</h3>' +
    N'<table border="1" cellpadding="4" style="border-collapse:collapse;">' +
    N'<tr><th>Solution</th><th>Status ID</th><th>Expiration</th></tr>' +
    STRING_AGG(
        N'<tr><td>' + SolutionName + N'</td><td>' + StatusID + N'</td><td>' + ExpirationDate + N'</td></tr>',
        CHAR(13) + CHAR(10)
    ) +
    N'</table></body></html>'
FROM UserSolutionData;

-- 发送邮件
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '你的邮件配置文件名',
    @recipients = @RecipientEmail,
    @subject = '解决方案过期提醒',
    @body = @HtmlBody,
    @body_format = 'HTML';

3. 定时触发邮件 #

用SQL Server Agent创建定时作业：

1. 新建作业，添加执行上述邮件发送逻辑的步骤（可通过游标循环给每个用户发送专属邮件）
2. 设置作业计划，按需求触发（如每天凌晨检查即将过期的解决方案）

四、常见问题解释 #

• PRINT (SELECT * FROM #temp)报错：PRINT只能输出单个标量值，不能直接打印结果集，必须将结果集拼接成字符串变量后再打印
• 避免代码冗余：通过CTE统一计算列的最大长度，后续新增列只需在ColumnLengths中添加对应长度计算，无需重复编写补空格逻辑

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