MySQL 匹配连接表中的最长前缀

Question

我们有 2 个表，“callsdb”保存电话通话日志，“regionsdb”保存区域相关前缀。我们需要做的是生成一个与“callsdb.destination_number”匹配的连接列表，其LONGEST前缀为“regionsdb.country_prefix”。 p>

注释:

1) “callsdb”中的电话号码可能有也可能没有有“1001”前缀。

2) “callsdb”中的电话号码在国家/地区前缀之前有一个额外的“00”前缀。

我们尝试了以下查询，但它的性能极差。有没有更快的方法来实现这一目标？

SELECT 
    uid, call_date, destination, name, country_prefix, duration, unit_cost 
FROM 
    callsdb.calls 
INNER JOIN 
    regionsdb.regions 
ON 
(
    (
        (calls.destination_number LIKE CONCAT('100100', regions.country_prefix, '%')) 
        AND 
        (
            LENGTH(regions.country_prefix) = 
            (
                SELECT MAX(LENGTH(regions.country_prefix))
                FROM regionsdb.regions
                WHERE calls.destination_number LIKE CONCAT('100100', regions.country_prefix, '%')
            )
        )
    )
    OR  
    (
        (calls.destination_number LIKE CONCAT('00', regions.country_prefix, '%')) 
        AND 
        (
            LENGTH(regions.country_prefix) = 
            (
                SELECT MAX(LENGTH(regions.country_prefix))
                FROM regionsdb.regions
                WHERE calls.destination_number LIKE CONCAT('00',regions.country_prefix, '%')
            )
        )
    )
)
ORDER BY call_date DESC;

编辑:

通过更改查询的“ON”部分来减少执行时间。但它仍然太慢了!

ON 
(
    (
        (IF(calls.destination_number LIKE '1001%', SUBSTRING(calls.destination_number, 5), calls.destination_number) LIKE CONCAT('00', regions.country_prefix, '%')) 
        AND 
        (
            LENGTH(regions.country_prefix) = 
            (
                SELECT MAX(LENGTH(regions.country_prefix))
                FROM regionsdb.regions
                WHERE IF(calls.destination_number LIKE '1001%', SUBSTRING(calls.destination_number, 5), calls.destination_number) LIKE CONCAT('00', regions.country_prefix, '%')
            )
        )
    )
)

编辑2:在选择中将“dst”更正为“目的地”。

Answer 1

您可以将group by与group_concat结合使用，它允许按降序连接regions表中的字段country_prefix 长度。然后您可以提取其中的第一个。

我假设字段name、unit_cost来自regions表，如果其他字段也是如此，则应用相同的逻辑对于他们来说。 group by 子句应列出 calls 表中所有选定的字段，或者至少列出关键字段:

select     c.uid, c.call_date, c.dst, 
           substring_index(
                group_concat(r.name order by length(r.country_prefix) desc), 
                ',', 1) as name, 
           substring_index(
                group_concat(r.country_prefix order by length(r.country_prefix) desc), 
                ',', 1) as country_prefix, 
           c.duration,
           substring_index(
                group_concat(format(r.unit_cost, 2) 
                             order by length(r.country_prefix) desc),
                ',', 1) as unit_cost 
from       callsdb.calls c
inner join regionsdb.regions r
        on (left(c.destination_number, 6 + length(r.country_prefix))
              = concat('100100', r.country_prefix)
           or mid(c.destination_number, 3, length(r.country_prefix))
              = r.country_prefix
           )
group by   c.uid,
           c.call_date,
           c.dst,
           c.duration