T-SQL中的单行表达式

Question

首先，我不确定这在T-SQL中正确的术语是什么(包括我在标题中放入的术语)，所以请尽量满足我的要求。我正在为联系人查找操作，在我们没有任何类型的唯一id(如ssn，驾驶执照等)的情况下，可以用来找到人，而不是我们必须使用姓名，电子邮件，工作，电话号码等。
目前，我有一个这样的查询:

SELECT c.id,
((CASE WHEN c.firstname = @firstname THEN 100
WHEN c.firstname LIKE @firstname+'%' THEN 50
ELSE 0
END
)
+
(CASE WHEN c.lastname = @lastname THEN 100
WHEN c.lastname LIKE @lastname+'%' THEN 50
ELSE 0
END
)) AS score
--- etc
FROM contacts c
WHERE ... -- using exact matches to reduce the total search space
ORDER BY score DESC

大致是如何工作的。对每一行做一些比较，用这些来计算总分，然后按最高分排序。为了使它更快，我在WHERE子句中对已索引的行使用精确比较，以减少必须进行的比较总数。

现在我发现的是，如果我们向一个人展示搜索结果，并要求他们做出最终选择，是否已经在我们的系统中联系，那么这工作得很好。然而，如果我们试图自动化它，那么我发现仅仅使用分数是不够的。我认为我还需要的是每行的完整和部分匹配的计数。

那么看看上面的查询:

IF c.fistname = @firstname  -- fullmatches+=1
IF c.firstname LIKE @firstName+'%' --partialmatchs+=1

然而，根据我目前所知道的，在选择查询中做到这一点的唯一方法是复制并传递case语句(在现实生活中要长得多)，并且只是改变变量，以便计算每个匹配，我无法表达我不想做多少。除了select语句，我知道我还可以将结果保存到一个表变量中，并对其进行一系列循环操作。然而，由于微软使执行单行操作一个该死的噩梦，我真的不想要这样做。UDF可能是一种可能性，但我宁愿避免使用它们。

我希望我能做这样的事情

SELECT 
SET @score = 0;
SET @fMatch = 0;
SET @pMatch = 0;
c.id,
((CASE WHEN c.firstname = @firstname THEN SET @score += 100; SET @fMatch+= 1
WHEN c.firstname LIKE @firstname+'%' THEN @score += 50; SET @pMatch+= 1
ELSE 0
END
)
+
(CASE WHEN c.lastname = @lastname THEN @score += 100;  SET @fMatch+= 1
WHEN c.lastname LIKE @lastname+'%' THEN @score+= 50; SET @pMatch+= 1
ELSE 0
END
))
,@score AS SCORE
,@fMatch AS FullMatches
,@pMatch AS PartialMatches
--- etc
FROM contacts c
WHERE
ORDER BY score DESC

感谢你们提供的任何帮助。

Answer 1

如果我理解了你的问题，这是一种方法。

SELECT Matches.ContactID,
Sum(Matches.FullMatch) AS FullMatchCount,
Sum(Matches.PartialMatch) AS PartialMatchCount,
Sum(MatchScore) as TotalMatchScore
FROM (SELECT c.id AS ContactID,
(CASE WHEN c.firstname = @firstname 
OR c.lastname = @lastname
THEN 1
ELSE 0
END
) AS FullMatch,
(CASE WHEN c.firstname like @firstname  + '%' 
OR c.lastname like @lastname + '%'
THEN 1
ELSE 0
END
) AS PartialMatch,
(CASE WHEN c.firstname = @firstname 
OR c.lastname = @lastname
THEN 100
WHEN c.firstname like @firstname  + '%' 
OR c.lastname like @lastname + '%'
THEN 50
ELSE 0
END
) AS MatchScore
FROM contacts c
) Matches
GROUP BY Matches.ContactID