法一：

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;