在MySQL中比较可能不匹配的记录

c0vxltue  于 12个月前  发布在  Mysql
关注(0)|答案(2)|浏览(129)

我有一个用于电话计费系统的数据库表。费率由找到的最长前缀匹配,因此可以对某些号码使用更具体的费率。假设该表如下所示:
| 柱|类型|
| --|--|
| ID|串行|
| 前缀|VARCHAR(15)|
| 率|十进制(5,5)|
| startDate|日期|
我从一个新的供应商导入费率,需要准备一份报告,将旧费率与新费率进行比较。这很容易根据日期进行自加入:

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;

字符串
这福尔斯的不同之处在于新旧提供者的前缀并不总是对齐,因此我的join并不总是拾取匹配的记录。例如,对于这些记录:
| ID|前缀|率|startDate|
| --|--|--|--|
| 1 | 1250 |0.02个单位|2020-01-01 2020-01-01|
| 2 | 1250208 |零点零三|2020-01-01 2020-01-01|
| 101 | 1250 |零点零一|2024-01-01 2024-01-01|
我想看到1-250开头的电话的费率下降了0.01,这包括在结果中。但我也想看到1-250-208开头的电话的费率下降了0.02。
同样,对于这些记录:
| ID|前缀|率|startDate|
| --|--|--|--|
| 8 | 1604 |零点零一|2020-01-01 2020-01-01|
| 120 | 1604 |0.02个单位|2024-01-01 2024-01-01|
| 121 | 1604463 |0.025| 2024-01-01 2024-01-01|
我想看到1-604的电话上升了0.01,但1-604-463的电话也上升了0.015。
请注意,这里的数字只是示例;我们的费率表的前缀最长为27861046247(南非移动的),最短为790(俄罗斯移动的)。
我如何改变JOIN(或者这是错误的方法?)来在两个方向上拉入适当的记录?我不关心性能或在一个查询中获得所有内容,因为这是一次性的手动过程。
小提琴在这里

qyuhtwio

qyuhtwio1#

添加另一个右连接t1和t2,并使用union删除重复的数据。您可以这样写:

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 (SUBSTRING(t1.prefix, 1, 4) = t2.prefix)
WHERE ABS(t1.rate - t2.rate) > 0
UNION
SELECT t2.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
    RIGHT JOIN (
        SELECT prefix,
            rate
        FROM testing
        WHERE startdate = "2024-01-01"
    ) t2 ON (SUBSTRING(t2.prefix, 1, 4) = t1.prefix)
WHERE ABS(t1.rate - t2.rate) > 0;

字符串

oxcyiej7

oxcyiej72#

您可以修改查询以在join子句中使用一个条件,该条件检查新前缀是否以旧前缀开头。

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 t2.prefix LIKE CONCAT(t1.prefix, '%')
WHERE ABS(t1.rate - t2.rate) > 0;

字符串
此查询在join子句中使用LIKE条件,即使新前缀扩展旧前缀,也允许匹配记录。CONCAT(t1.prefix,' %')确保我们匹配的前缀以旧前缀开头。

相关问题