如何用T-SQL筛选phone列中全为连续数字的记录？

要筛选出phone列里所有数字完全连续递增（如1234567）或递减（如876543）的记录，只要有任意相邻数字不连续（比如1234568、1245689）就排除，你可以用递归CTE拆分字符并校验差值的方式实现：

核心解决方案 #

假设你的表名为PhoneNumbers，phone列是字符串类型（如果是数值型，先转成字符串处理），以下是完整查询：

WITH PhoneDigits AS (
    SELECT
        phone,
        SUBSTRING(phone, 1, 1) AS digit,
        1 AS position,
        LEN(phone) AS total_length
    FROM PhoneNumbers
    WHERE LEN(phone) >= 2 -- 单个数字无连续意义，直接过滤
    UNION ALL
    SELECT
        pd.phone,
        SUBSTRING(pd.phone, pd.position + 1, 1),
        pd.position + 1,
        pd.total_length
    FROM PhoneDigits pd
    WHERE pd.position < pd.total_length
),
DigitDifferences AS (
    SELECT
        phone,
        CAST(d2.digit AS INT) - CAST(d1.digit AS INT) AS diff
    FROM PhoneDigits d1
    JOIN PhoneDigits d2 ON d1.phone = d2.phone AND d2.position = d1.position + 1
)
SELECT DISTINCT phone
FROM DigitDifferences
GROUP BY phone
HAVING 
    -- 所有相邻数字差为1（递增）
    (COUNT(CASE WHEN diff = 1 THEN 1 END) = COUNT(*))
    -- 或者所有相邻数字差为-1（递减）
    OR (COUNT(CASE WHEN diff = -1 THEN 1 END) = COUNT(*));

代码解释 #

1. PhoneDigits CTE：递归拆分每个phone字符串的每一位数字，记录当前字符的位置和字符串总长度，同时过滤掉长度小于2的记录。
2. DigitDifferences CTE：通过自连接匹配相邻位置的字符，计算后一位数字减前一位数字的差值。
3. 最终查询：按phone分组后，校验所有差值是否全为1（递增）或全为-1（递减），满足条件的记录即为目标结果。

额外适配场景 #

1. 处理数值型phone列 #

如果phone是INT/BIGINT类型，先转成字符串避免丢失前导零（如果需要保留），同时过滤非数字内容：

WITH PhoneNumbersClean AS (
    SELECT
        CAST(phone AS VARCHAR(20)) AS phone
    FROM YourTableName
    -- 过滤包含非数字的记录，以及长度小于2的记录
    WHERE phone NOT LIKE '%[^0-9]%' AND LEN(CAST(phone AS VARCHAR(20))) >= 2
),
PhoneDigits AS (
    SELECT
        phone,
        SUBSTRING(phone, 1, 1) AS digit,
        1 AS position,
        LEN(phone) AS total_length
    FROM PhoneNumbersClean
    UNION ALL
    SELECT
        pd.phone,
        SUBSTRING(pd.phone, pd.position + 1, 1),
        pd.position + 1,
        pd.total_length
    FROM PhoneDigits pd
    WHERE pd.position < pd.total_length
),
DigitDifferences AS (
    SELECT
        phone,
        CAST(d2.digit AS INT) - CAST(d1.digit AS INT) AS diff
    FROM PhoneDigits d1
    JOIN PhoneDigits d2 ON d1.phone = d2.phone AND d2.position = d1.position + 1
)
SELECT DISTINCT phone
FROM DigitDifferences
GROUP BY phone
HAVING 
    (COUNT(CASE WHEN diff = 1 THEN 1 END) = COUNT(*))
    OR (COUNT(CASE WHEN diff = -1 THEN 1 END) = COUNT(*));

2. 测试示例 #

假设表中有以下数据：

查询结果会返回：1234567、876543、987（111的差值全为0，不满足递增/递减条件，会被排除）。

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