SQL Server中如何在CASE表达式ELSE分支设置起始为100的自增值

要解决这个问题，我们可以结合TRY_CAST（用于可靠判断数值有效性）和**ROW_NUMBER()**窗口函数（生成递增序列）来实现需求，替代无法自动递增的CASE表达式。

解决方案步骤 #

1. 准备临时表（若未创建） #

假设你的临时表结构和源表匹配，示例如下：

CREATE TABLE tempTable (
    CustID INT,
    CustName VARCHAR(100) -- 根据实际业务字段调整
);

2. 核心插入语句 #

使用CTE（公共表表达式）先预处理数据，再插入临时表：

WITH CustomerPreprocess AS (
    SELECT
        CustID,
        CustName,
        -- 标记当前CustID是否可转换为有效整数
        CASE WHEN TRY_CAST(CustID AS INT) IS NOT NULL THEN 1 ELSE 0 END AS IsValidNumeric,
        -- 仅对非数值的行生成递增序号（从1开始）
        ROW_NUMBER() OVER (
            PARTITION BY CASE WHEN TRY_CAST(CustID AS INT) IS NOT NULL THEN 1 ELSE 0 END
            ORDER BY (SELECT NULL) -- 若需指定递增顺序，替换为实际字段如CustName/CreateTime
        ) AS SeqNum
    FROM customerTable
)
INSERT INTO tempTable (CustID, CustName)
SELECT
    CASE
        WHEN IsValidNumeric = 1 THEN TRY_CAST(CustID AS INT) -- 数值型直接保留
        ELSE 99 + SeqNum -- 非数值型从100开始递增（1+99=100）
    END AS CustID,
    CustName
FROM CustomerPreprocess;

关键细节说明 #

• TRY_CAST替代ISNUMERIC：ISNUMERIC会误判部分特殊字符（如'123.0'、'+123'）为有效数值，而TRY_CAST仅在能成功转换为INT时返回值，否则返回NULL，判断更准确。
• PARTITION BY分组：将数值型和非数值型数据分成两组，确保ROW_NUMBER仅在非数值组内递增，不影响数值型数据的原有ID。
• 递增起始值调整：若需要避免与原有数值ID冲突（比如原有ID已存在≥100的值），可将99替换为(SELECT ISNULL(MAX(CustID), 99) FROM customerTable WHERE TRY_CAST(CustID AS INT) IS NOT NULL)，这样递增序列会从原有最大ID+1开始。

示例验证 #

假设源表customerTable数据：

执行插入后，tempTable结果：

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