sql-server - SQL Server Best of X 比赛

Question

我正在尝试执行一个查询，该查询可以返回 5 个条件中的大多数匹配的结果。但如果只有 5 场比赛中的 5 场比赛，则优先。

为了说明我的问题，已准备好以下 SQL。

declare @tmp table (
id int identity
,field1 nvarchar(60)
,field2 nvarchar(60)
,field3 nvarchar(60)
,field4 nvarchar(60)
,field5 nvarchar(60)
)

insert into @tmp values
    ('Bob','Jones','Mr','000001','bob@example.com')
insert into @tmp values
    ('Bill','Jones','','000002','bill@example.com')
insert into @tmp values
    ('Sue','Jones','Mrs','000003','jones@example.com')
insert into @tmp values
    ('Adrian','Jones','','000001','jones@example.com')
insert into @tmp values
    ('Bertha','Jones','Mrs','000001','jones@example.com')

select *
from @tmp

declare @key1 nvarchar(60), @key2 nvarchar(60), @key3 nvarchar(60), @key4 nvarchar(60), @key5 nvarchar(60)

select
    @key1 = 'Bertha'
    ,@key2 = 'Jones'
    ,@key3 = 'Mrs'
    ,@key4 = '000001'
    ,@key5 = 'jones@example.com'

select
    *
    ,case when field1 = @key1 then 1 else 0 end as X1
    ,case when field2 = @key2 then 1 else 0 end as X2
    ,case when field3 = @key3 then 1 else 0 end as X3
    ,case when field4 = @key4 then 1 else 0 end as X4
    ,case when field5 = @key5 then 1 else 0 end as X5
from @tmp

如果您查看结果，您会看到第 3 行和第 4 行在 3 个字段上有多个匹配，但第 5 行在 5 个字段上匹配。因此，这是一个相同的匹配项，这就是我要返回的匹配项。

但是，如果未插入第 5 行，则第 3 行和第 4 行是最佳匹配项，在这种情况下，我希望将它们返回。

我一直在思考如何最好地做到这一点，如果这能有所作为，我正在使用 SQL Server 2008。

在实际场景中，并不是像本例那样都是简单的case语句，而是分选到其他表中。

我研究过 group by 和 having，但我看不出如何在这种情况下使用它们。

如何在 SQL Server 中像这样跨多个条件进行“最佳”类型匹配？

如果这看起来像页面告诉我的那样“主观”，请说出来，我会删除它。但我不认为这是主观的，因为这是我认为通常需要的 SQL 重复数据删除方案。

Answer 1

考虑将您的 x 列滚动到 SUM 中以获得分数。这是一个 CTE(通用表表达式)，您可以从中进行查询。

;with mysum AS (
select    *
    ,case when field1 = @key1 then 1 else 0 end as X1
    ,case when field2 = @key2 then 1 else 0 end as X2
    ,case when field3 = @key3 then 1 else 0 end as X3
    ,case when field4 = @key4 then 1 else 0 end as X4
    ,case when field5 = @key5 then 1 else 0 end as X5
from @tmp)

SELECT id, field1, field2, field3, field4, field5, 
       SUM(x5+x4+x3+x2+x1) AS MatchScore 
FROM MySum
GROUP BY id, field1, field2, field3, field4, field5
ORDER BY MatchScore DESC