如何用SQL为指定字段补零并生成两个输出表？

输入表SQL代码 #

declare @mytable table
(
REG INT,
ISTR INT,
DAT DATETIME,
TRN INT
)
INSERT @mytable
SELECT 8,   1,      '2023-04-23 00:00:00',  776862 UNION ALL
SELECT 03,  238,    '2023-04-23 00:00:00',  300139 UNION ALL
SELECT 3,   238,    '2023-04-23 00:00:00',  300139 UNION ALL
SELECT 12,  172,    '2023-04-23 00:00:00',  237257 UNION ALL
SELECT 6,   1,      '2023-04-23 00:00:00',  849848

SELECT * FROM @mytable

需求说明 #

需要生成两个输出表，两者的字段格式化规则完全一致：

• REG：补零至3位，不足则前置补零
• ISTR：补零至5位，不足则前置补零
• DAT：格式化为YYYYMMDDHHmmss格式的14位字符串
• TRN：补零至10位，不足则前置补零
• 最终每个记录输出拼接后的完整字符串，同时保留原字段

其中：

• Original表：仅保留处理后唯一的记录
• Duplicate表：仅保留处理后重复出现的记录

实现方案（SQL Server） #

完全可以通过SQL实现，利用字符串格式化函数和窗口函数即可完成，具体代码如下：

-- 先生成格式化后的中间数据，同时统计每条记录的重复次数
WITH FormattedData AS (
    SELECT 
        -- 拼接格式化后的字段
        RIGHT('000' + CAST(REG AS VARCHAR(3)), 3) +
        RIGHT('00000' + CAST(ISTR AS VARCHAR(5)), 5) +
        FORMAT(DAT, 'yyyyMMddHHmmss') +
        RIGHT('0000000000' + CAST(TRN AS VARCHAR(10)), 10) AS CombinedField,
        -- 保留原字段
        REG, ISTR, DAT, TRN,
        -- 统计每个格式化键的出现次数
        COUNT(*) OVER (
            PARTITION BY 
                RIGHT('000' + CAST(REG AS VARCHAR(3)), 3),
                RIGHT('00000' + CAST(ISTR AS VARCHAR(5)), 5),
                FORMAT(DAT, 'yyyyMMddHHmmss'),
                RIGHT('0000000000' + CAST(TRN AS VARCHAR(10)), 10)
        ) AS RecordCount
    FROM @mytable
)

-- 生成Original表：提取唯一记录
SELECT CombinedField, REG, ISTR, DAT, TRN
INTO Original
FROM FormattedData
WHERE RecordCount = 1;

-- 生成Duplicate表：提取重复记录
SELECT CombinedField, REG, ISTR, DAT, TRN
INTO Duplicate
FROM FormattedData
WHERE RecordCount > 1;

关键函数说明 #

• RIGHT('000' + CAST(REG AS VARCHAR(3)), 3)：先把数字转为字符串，前面拼接足够的零，再取右侧指定长度，确保最终字符串长度符合要求
• FORMAT(DAT, 'yyyyMMddHHmmss')：SQL Server 2012及以上版本支持的日期格式化函数，直接输出指定格式的14位字符串
• COUNT(*) OVER (PARTITION BY ...)：窗口函数，按格式化后的字段分组统计次数，用来区分唯一和重复记录

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