sql - 如何根据电话号码找到长途费率的最佳匹配？

Question

我需要找出最好的方法来做到这一点 - 这是一个例子。假设您要调用英格兰:，您要调用的号码是:447400123456。现在您可以使用三个不同的长途电话提供商，但它们的费率都有所不同。有一个 LD 率表，如下所示:

VendorID    Dialcode    Description             Rate
------------------------------------------------------
1           44          UK - All                0.01
1           447         UK - Mobile             0.02
2           44          UK - All                0.015
3           44          UK - All                0.013
3           44740       UK - Mobile - Hutch     0.01

我需要一个可以匹配并返回每个供应商电话号码最大位数的查询 - 因此对于上面的号码应该返回:

VendorID    Dialcode    Description             Rate
------------------------------------------------------
1           447         UK - Mobile             0.02
2           44          UK - All                0.015
3           44740       UK - Mobile - Hutch     0.01

我希望除了用循环磨掉数字之外还可以做一些事情。另外，如果表上应该有一个索引来加快处理速度 - 您有何建议？

Answer 1

您可以使用ROW_NUMBER() OVER(PARTITION BY VendorID ORDER BY LEN(DialCode)来获取每个VendorID的最大位数:

SQL Fiddle

DECLARE @telNo VARCHAR(50) = '447400123456';
WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY VendorID ORDER BY LEN(DialCode) DESC)
    FROM tbl
    WHERE @telNo LIKE DialCode + '%'
)
SELECT
    VendorID, DialCode, Description, Rate
FROM Cte
WHERE RN = 1

结果:

| VendorID | DialCode |         Description |  Rate |
|----------|----------|---------------------|-------|
|        1 |      447 |         UK - Mobile |  0.02 |
|        2 |       44 |            UK - All | 0.015 |
|        3 |    44740 | UK - Mobile - Hutch |  0.01 |