代码之家 › 专栏 › 技术社区 › Daniel James Bryars

在mssqlserver2008中,加入“最佳”匹配的好方法是什么?

sqlclr join tsql c#

2

Daniel James Bryars · 技术社区 · 14 年前

实际上,我希望根据“Call”表中的TelephoneNumber字段从“Rate”表中选择前缀的最佳匹配。给出下面的示例数据,“0123456789”最匹配前缀“012”,而“0100000000”最匹配前缀“01”。

我在SQL注释中包含了一些DML和一些正确匹配的示例。

rate表中大约有70000行,call表中大约有2000万行。但是基于dateTime列的Select在Call表中有一个限制,所以实际上查询只需要运行50万个调用行。

速率表中的前缀最长可达16个字符。

我不知道如何在SQL中实现这一点,我正在考虑编写一个C#SQLCLR函数来实现这一点。有人做过类似的事吗?如果你有任何建议,我将不胜感激。

示例数据

呼叫表:

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”与空白字符串匹配,因为没有更好的匹配。

4 回复 | 直到 14 年前

1

Adam Robinson 14 年前

因为这是基于部分匹配的,所以subselect是唯一可行的选择(除非像LukeH假设的那样,每个调用都是唯一的)

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

2

1

LukeH 14 年前

SELECT t.Id, t.TelephoneNumber, t.Prefix, t.Scale
FROM
(
    SELECT *, ROW_NUMBER() OVER
              (
                  PARTITION BY c.TelephoneNumber
                  ORDER BY r.Scale DESC
              ) AS RowNumber
    FROM [call] AS c
        INNER JOIN [rate] AS r
            ON c.TelephoneNumber LIKE r.Prefix + '%'
) AS t
WHERE t.RowNumber = 1
ORDER BY t.Id

3

0

D'Arcy Rittich 14 年前

试试这个:

select Prefix, min(c.TelephoneNumber)
from Rate r
left outer join Call c on c.TelephoneNumber like left(Prefix + '0000000000', 10) 
    or c.TelephoneNumber like Prefix + '%'
group by Prefix

4

0

Damien_The_Unbeliever 14 年前

您可以使用左连接来尝试寻找“更好”的匹配,然后在where子句中消除此类匹配。例如。:

select
 *
from
 Call c
  inner join
 Rate r
  on
   r.Prefix = SUBSTRING(c.TelephoneNumber,1,LEN(r.Prefix))
  left join
 Rate r_anti
  on
   r_anti.Prefix = SUBSTRING(c.TelephoneNumber,1,LEN(r_anti.Prefix)) and
   LEN(r_anti.Prefix) > LEN(r.Prefix)
where
 r_anti.Prefix is null