SQL Server 多条件查询并按匹配度排序

在很多的列表页面可能有这类需求，例如在资产查询时按照资产名称（Name）或构造（Make）或型号（Model）组合查询，查询结果按照匹配程度进行排序，匹配项最多的排在最前面。

我们可以借助case when then end 进行单语句查询，如下：

select AssetId,Name,Make,Model from zion.Asset
where Name like 'Navarro Fan%' or Make = 'GREENHECK' or Model = 'CSP-200-QD'
order by (
case when Name like 'Navarro Fan%' then 1 else 0 end +
case when Make = 'GREENHECK' then 1 else 0 end +
case when Model = 'CSP-200-QD' then 1 else 0 end
) desc,AssetId asc

我们也可以用如下方式的嵌套查询，不过第一种方式可能效率更高：

select *
from(
select AssetId,Name,Make,Model,(
(case when Name like 'Navarro Fan%' then 1 else 0 end)
+(case when Make = 'GREENHECK' then 1 else 0 end)
+(case when Model = 'CSP-200-QD' then 1 else 0 end)
)
as cnt from zion.Asset) as temp
where cnt>0
order by cnt desc,AssetId asc

如果谁有更好的方式，欢迎留言！！！