MySQL中如何生成表中电话号码范围对应序列并查找重复号码

1. 更优雅的号码序列生成方案 #

你可以用内置数字序列+行转列的方式替代硬编码的多段UNION ALL，代码更简洁易维护，不需要额外创建辅助表：

• 首先用VALUES行构造器完成4个phone字段的行转列，比写4次UNION ALL更精简
• 构造0-9的数字临时序列匹配号码范围，直接完成范围展开，不需要自定义函数或循环逻辑

通用SQL示例（支持MySQL 8.0+/PostgreSQL/SQL Server等主流数据库）：

-- 生成0-9的数字序列，匹配最多10个的号码范围
WITH num_seq AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9
)
SELECT 
    -- 拼接前缀和序列数字生成完整号码
    CONCAT(
        SUBSTRING_INDEX(p.phone_raw, '-', 1),
        num_seq.n
    ) AS full_phone
FROM (
    -- 行转列合并4个phone字段，过滤空值
    SELECT t.id, p.phone_raw
    FROM 你的表名 t
    JOIN (
        VALUES (phone1), (phone2), (phone3), (phone4)
    ) p(phone_raw)
    WHERE p.phone_raw IS NOT NULL AND p.phone_raw != ''
) p
-- 匹配号码范围的起止数字
JOIN num_seq ON num_seq.n BETWEEN 
    RIGHT(SUBSTRING_INDEX(p.phone_raw, '-', 1), 1) + 0 
    AND 
    SUBSTRING_INDEX(p.phone_raw, '-', -1) + 0

如果使用的是不支持VALUES行构造器的旧版本数据库（如MySQL 5.7），行转列部分可以换回UNION ALL写法，号码范围展开的逻辑仍然可以复用上面的数字序列方案。

2. 重复号码查找优化方案 #

你当前的UNION ALL+GROUP BY逻辑是可行的，结合上面的新写法可以进一步简化代码，同时查询效率对数百行的小表来说没有明显损耗，直接在展开后的结果上加分组过滤即可：

WITH num_seq AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9
),
all_full_phones AS (
    SELECT 
        CONCAT(
            SUBSTRING_INDEX(p.phone_raw, '-', 1),
            num_seq.n
        ) AS full_phone
    FROM (
        SELECT t.id, p.phone_raw
        FROM 你的表名 t
        JOIN (
            VALUES (phone1), (phone2), (phone3), (phone4)
        ) p(phone_raw)
        WHERE p.phone_raw IS NOT NULL AND p.phone_raw != ''
    ) p
    JOIN num_seq ON num_seq.n BETWEEN 
        RIGHT(SUBSTRING_INDEX(p.phone_raw, '-', 1), 1) + 0 
        AND 
        SUBSTRING_INDEX(p.phone_raw, '-', -1) + 0
)
-- 分组过滤重复号码
SELECT full_phone, COUNT(*) AS 出现次数
FROM all_full_phones
GROUP BY full_phone
HAVING COUNT(*) > 1

如果使用支持窗口函数的数据库版本，也可以用窗口函数实现重复检测，不需要单独分组，写法更灵活：

WITH num_seq AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9
),
all_full_phones AS (
    SELECT 
        CONCAT(
            SUBSTRING_INDEX(p.phone_raw, '-', 1),
            num_seq.n
        ) AS full_phone
    FROM (
        SELECT t.id, p.phone_raw
        FROM 你的表名 t
        JOIN (
            VALUES (phone1), (phone2), (phone3), (phone4)
        ) p(phone_raw)
        WHERE p.phone_raw IS NOT NULL AND p.phone_raw != ''
    ) p
    JOIN num_seq ON num_seq.n BETWEEN 
        RIGHT(SUBSTRING_INDEX(p.phone_raw, '-', 1), 1) + 0 
        AND 
        SUBSTRING_INDEX(p.phone_raw, '-', -1) + 0
),
phone_with_cnt AS (
    SELECT full_phone, COUNT(*) OVER(PARTITION BY full_phone) AS 出现次数
    FROM all_full_phones
)
SELECT DISTINCT full_phone, 出现次数
FROM phone_with_cnt
WHERE 出现次数 > 1

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