MySQL中非匹配前缀的电话费率对比方案问询

我有一个电话计费系统的MySQL数据库表，费率按最长匹配前缀规则生效，以实现特定号码使用更精准的费率。表结构如下：

我正在导入新供应商的费率，需要生成新旧费率对比报告。原本用日期自连接的SQL如下：

SELECT t1.prefix, t1.rate AS old_rate, t2.rate AS new_rate, t2.rate - t1.rate AS diff
FROM (SELECT prefix, rate FROM testing WHERE startdate = "2020-01-01") t1
LEFT JOIN (SELECT prefix, rate FROM testing WHERE startdate = "2024-01-01") t2
  ON (t1.prefix = t2.prefix)
WHERE ABS(t1.rate - t2.rate) > 0;

但这个方法存在问题：新旧供应商的前缀并不总是完全匹配，导致无法获取全部差异：

• 示例一：旧表有前缀1250（费率0.02）、1250208（费率0.03），新表仅前缀1250（费率0.01），需同时展示1250费率降0.01、1250208费率降0.02；
• 示例二：旧表有前缀1604（费率0.01），新表有前缀1604（费率0.02）、1604463（费率0.025），需同时展示1604费率涨0.01、1604463费率涨0.015。

注：实际前缀长度范围从790（俄罗斯移动）到27861046247（南非移动）不等。

需要修改JOIN语句或更换方案，双向获取所有对应的费率差异记录。该操作为一次性手动流程，无需考虑性能。

要实现符合最长匹配规则的费率对比，需要分别为每个旧前缀找到新表中的最长匹配前缀，同时为每个新前缀找到旧表中的最长匹配前缀，再合并结果并筛选出有差异的记录。

完整SQL方案 #

WITH old_to_new AS (
    SELECT
        t1.prefix AS prefix,
        t1.rate AS old_rate,
        COALESCE(t2.rate, 0) AS new_rate,
        COALESCE(t2.rate, 0) - t1.rate AS diff
    FROM (SELECT prefix, rate FROM testing WHERE startdate = "2020-01-01") t1
    LEFT JOIN (SELECT prefix, rate FROM testing WHERE startdate = "2024-01-01") t2
        ON t1.prefix LIKE CONCAT(t2.prefix, '%')
    WHERE NOT EXISTS (
        SELECT 1
        FROM (SELECT prefix, rate FROM testing WHERE startdate = "2024-01-01") t3
        WHERE t1.prefix LIKE CONCAT(t3.prefix, '%') AND LENGTH(t3.prefix) > LENGTH(t2.prefix)
    )
),
new_to_old AS (
    SELECT
        t2.prefix AS prefix,
        COALESCE(t1.rate, 0) AS old_rate,
        t2.rate AS new_rate,
        t2.rate - COALESCE(t1.rate, 0) AS diff
    FROM (SELECT prefix, rate FROM testing WHERE startdate = "2024-01-01") t2
    LEFT JOIN (SELECT prefix, rate FROM testing WHERE startdate = "2020-01-01") t1
        ON t2.prefix LIKE CONCAT(t1.prefix, '%')
    WHERE NOT EXISTS (
        SELECT 1
        FROM (SELECT prefix, rate FROM testing WHERE startdate = "2020-01-01") t3
        WHERE t2.prefix LIKE CONCAT(t3.prefix, '%') AND LENGTH(t3.prefix) > LENGTH(t1.prefix)
    )
)
SELECT DISTINCT *
FROM (
    SELECT * FROM old_to_new
    UNION ALL
    SELECT * FROM new_to_old
) combined
WHERE ABS(diff) > 0
ORDER BY prefix;

逻辑说明 #

• 使用LIKE CONCAT(prefix, '%')匹配前缀关系，确保某前缀是另一前缀的开头；
• 通过NOT EXISTS子查询筛选出最长匹配前缀，符合计费系统的规则；
• 用COALESCE处理无匹配的情况（如新前缀在旧表无对应、旧前缀在新表无对应）；
• 通过UNION ALL合并双向结果，DISTINCT去重，最后筛选出费率有变化的记录。

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