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

排序後結果如圖: