MSSQL:获取基于查询条件的匹配百分比

Question

我有一个查询，搜索重复在我的数据库。我正在搜索基于名字，姓氏和出生日期。我要做的是显示百分比，如果三个都匹配，它会显示100%如果只匹配姓和名，它会显示66%等等。我不知道该怎么做。

我在Microsoft SQL上做这个

下面是我当前的查询:

SELECT DISTINCT t.CUSTID, t.FORENAME, t.SURNAME, t.GENDER, CONVERT(varchar, t.DATE_BIRTH, 103) as 
DOB 
FROM CUSTOMERS AS t 
INNER JOIN (
SELECT FORENAME, SURNAME, DATE_BIRTH
FROM CUSTOMERS
GROUP BY FORENAME, SURNAME, DATE_BIRTH
HAVING COUNT(*) > 1) AS td 
ON t.FORENAME = td.FORENAME AND t.SURNAME = td.SURNAME
AND t.DATE_BIRTH = td.DATE_BIRTH

Answer 1

你可以这样写:

select c.*, c2.*,
( (case when c.forename = c2.forename then 1.0 else 0 end) +
(case when c.surname = c2.surname then 1.0 else 0 end) +
(case when c.date_of_birth = c2.date_of_birth then 1.0 else 0 end)
) / 3 as ratio
from customers c join
customers c2
on c.forename = c2.forename or
c.surname = c2.surname or
c.date_of_birth = c2.date_of_birth
order by ratio desc;

这将是无效的。此外，每个客户都将与自身进行比较，因此结果集将很大。