SQL Server如何从三个手机号列中返回首个10位或最长号码，优先手机号

实现代码 #

SELECT 
    p.first_name,
    t.selected_number
FROM dbo.Persons p
CROSS APPLY (
    SELECT TOP 1 number AS selected_number
    FROM (
        -- 拆分三个手机号列为行数据，标注列优先级、计算号码长度
        VALUES 
            (cell_number, 1, LEN(cell_number)),
            (home_number, 2, LEN(home_number)),
            (work_number, 3, LEN(work_number))
    ) AS v(number, priority, len_num)
    -- 过滤空号码
    WHERE number IS NOT NULL
    ORDER BY 
        -- 第一优先级：10位号码排最前
        CASE WHEN len_num = 10 THEN 0 ELSE 1 END ASC,
        -- 第二优先级：号码长度倒序，无10位时取最长
        len_num DESC,
        -- 第三优先级：同条件下优先取cell_number列
        priority ASC
) t

如果仅需要全局返回唯一1个符合要求的号码，可在最外层查询加TOP 1：

SELECT TOP 1
    t.selected_number
FROM dbo.Persons p
CROSS APPLY (
    SELECT TOP 1 number AS selected_number
    FROM (
        VALUES 
            (cell_number, 1, LEN(cell_number)),
            (home_number, 2, LEN(home_number)),
            (work_number, 3, LEN(work_number))
    ) AS v(number, priority, len_num)
    WHERE number IS NOT NULL
    ORDER BY 
        CASE WHEN len_num = 10 THEN 0 ELSE 1 END ASC,
        len_num DESC,
        priority ASC
) t

逻辑说明 #

• 通过VALUES子句将每行的三个手机号列转换为行级数据，方便统一排序筛选，同时为三个列设置优先级权重，cell_number优先级最高
• 先过滤所有空值号码，避免无效数据参与排序
• 三层排序规则完全匹配需求：优先返回10位号码，无10位号码时返回长度最长的号码，同条件下优先保留cell_number列的值
• 兼容场景：如果需要支持三个号码全为空的记录不被过滤，将CROSS APPLY替换为OUTER APPLY即可

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