在MS SQL Server 2008中加入“最佳"邮件服务器的好方法是什么?比赛? [英] What is a good approach in MS SQL Server 2008 to join on a "best" match?

问题描述

从本质上讲，我想基于通话"表中的电话号码"字段，从费率"表中选择前缀的最佳匹配.给定以下示例数据，"0123456789"将最匹配前缀"012"，而"0100000000"将最匹配前缀"01".

In essence I want to pick the best match of a prefix from the "Rate" table based on the TelephoneNumber field in the "Call" table. Given the example data below, '0123456789' would best match the prefix '012' whilst '0100000000' would best match the prefix '01'.

我在SQL注释中包括了一些DML和更多正确匹配的示例.

I've included some DML with some more examples of correct matches in the SQL comments.

费率表中大约有70,000行，而呼叫表中大约有2000万行.但是对基于dateTime列的从呼叫表中选择"有一个限制，因此实际上查询仅需要运行50万个呼叫行.

There will be circa 70,000 rows in the rate table and the call table will have around 20 million rows. But there will be a restriction on the Select from the Call table based on a dateTime column so actually the query will only need to run over 0.5 million call rows.

费率"表中的前缀最长为16个字符.

The prefix in the Rate table can be up to 16 characters long.

我不知道如何在SQL中实现此目的，我目前正在考虑编写C#SQLCLR函数来实现此目的.有人做过类似的事情吗?如果您有任何建议，我将不胜感激.

I have no idea how to approach this in SQL, I'm currently thinking of writing a C# SQLCLR function to do it. Has anyone done anything similar? I'd appreciate any advice you have.

通话表:

Id  TelephoneNumber
1   0123456789
2   0100000000
3   0200000000
4   0780000000
5   0784000000
6   0987654321

费率表:

Prefix Scale
       1
01   1.1
012 1.2
02   2
078    3
0784   3.1

DML

create table Rate
(
    Prefix nvarchar(16) not null,
    Scale float not null
)

create table [Call]
(
    Id bigint not null,
    TelephoneNumber nvarchar(16) not null
)

insert into Rate (Prefix, Scale) values ('', 1)
insert into Rate (Prefix, Scale) values ('01', 1.1)
insert into Rate (Prefix, Scale) values ('012', 1.2)
insert into Rate (Prefix, Scale) values ('02', 2)
insert into Rate (Prefix, Scale) values ('078', 3)
insert into Rate (Prefix, Scale) values ('0784', 3.1)

insert into [Call] (Id, TelephoneNumber) values (1, '0123456789') --match 1.2
insert into [Call] (Id, TelephoneNumber) values (2, '0100000000') --match 1.1
insert into [Call] (Id, TelephoneNumber) values (3, '0200000000') --match 2
insert into [Call] (Id, TelephoneNumber) values (4, '0780000000') --match 3
insert into [Call] (Id, TelephoneNumber) values (5, '0784000000') --match 3.1
insert into [Call] (Id, TelephoneNumber) values (6, '0987654321') --match 1

注意:最后一个'0987654321'与空白字符串匹配，因为没有更好的匹配项.

Note: The last one '0987654321' matches the blank string because there are no better matches.

推荐答案

由于这是基于部分匹配的，因此subselect是唯一可行的选项(除非像LukeH假定的那样，每个调用都是唯一的)

Since this is based on partial matching, a subselect would be the only viable option (unless, like LukeH assumes, every call is unique)

select
    c.Id,
    c.TelephoneNumber,
    (select top 1 
         Scale 

         from Rate r 

         where c.TelephoneNumber like r.Prefix + '%' order by Scale desc
    ) as Scale

from Call c

这篇关于在MS SQL Server 2008中加入“最佳"邮件服务器的好方法是什么?比赛?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！