根据匹配词个数排序

法一：

select title,(case when CHARINDEX('Log4net',title)>0 then 1 else 0 end )+(case when CHARINDEX('oracle',title)>0 then 1 else 0 end ) counts from T_KNOWLEDGE where title like '%Log4net%' or title like '%oracle%' order by counts desc

法二：

select title from T_KNOWLEDGE where title like '%Log4net%' or title like '%oracle%' order by (case when CHARINDEX('Log4net',title)>0 then 1 else 0 end )+(case when CHARINDEX('oracle',title)>0 then 1 else 0 end ) desc

 

带分页的：

with cte as(
select *,row_number() over(order by CREATED_TIME desc) rn from (select title,(case when CHARINDEX('Log4net',title)>0 then 1 else 0 end )+(case when CHARINDEX('oracle',title)>0 then 1 else 0 end ) counts from T_KNOWLEDGE where title like '%Log4net%' or title like '%oracle%' order by counts desc
) t
)select * from cte where rn between 1 and 10;