sqlserver使用order by case when进行优先级排序

--创建表
create table tNews
(Newsld int identity(1,1) primary key,
Keyword nvarchar(50) not null,
Title nvarchar(100) null,
Contents nvarchar(1000) null,
Remark nvarchar(1000))

--向表中插入数据
insert into tNews(Keyword,Title,Contents,Remark) values ('csd','x','x','a')
insert into tNews(Keyword,Title,Contents,Remark) values ('asd','ax','x','a')
insert into tNews(Keyword,Title,Contents,Remark) values ('cad','x','xa','a')
insert into tNews(Keyword,Title,Contents,Remark) values ('csdn','sdax','x','xy')
insert into tNews(Keyword,Title,Contents,Remark) values ('csdns','sdax','x','xy')
insert into tNews(Keyword,Title,Contents,Remark) values	('all3ns','s3x','3ax','xy')
insert into tNews(Keyword,Title,Contents,Remark) values	('没有','szx','mx','mei')

select * from tNews

--delete from tNews

排序Sql语句:

--关键字含有a的放在最前面的行,标题含有a的放在次于关键字含有a的行...没有含有a的放在最后
--依次排序为:Keyword>Title>Contents>Remark

 select Newsld,Keyword,Title,Contents,Remark from tNews
 order by 
 case when Keyword like '%a%' then 1 else 10 end,
 case when Title like '%a%' then 2 else 10 end,
 case when Contents like '%a%' then 3 else 10 end,
 case when Remark like '%a%' then 4 else 10 end

排序后结果如图: