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

c0vxltue 于 5个月前发布在 Mysql

关注(0)|答案(2)|浏览(52)

我有一个用于电话计费系统的数据库表。费率由找到的最长前缀匹配，因此可以对某些号码使用更具体的费率。假设该表如下所示：
| 柱|类型|
| --|--|
| 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（或者这是错误的方法？）来在两个方向上拉入适当的记录？我不关心性能或在一个查询中获得所有内容，因为这是一次性的手动过程。
小提琴在这里

mysql

来源：https://stackoverflow.com/questions/77649690/comparing-possibly-non-matching-records-in-mysql

2条答案

按热度按时间

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;

字符串

赞(0）回复(0）举报 5个月前

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，' %'）确保我们匹配的前缀以旧前缀开头。

赞(0）回复(0）举报 5个月前

我来回答

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

2条答案

相关问题

热门标签

最新问答