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

-- 创建表 create table tNews (NewsId 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,Re

 

--创建表

create table tNews

(NewsId 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 intotNews(Keyword,Title,Contents,Remark) values ('asd','ax','x','a')

insert intotNews(Keyword,Title,Contents,Remark) values ('cad','x','xa','a')

insert intotNews(Keyword,Title,Contents,Remark) values ('csadx','x','x','xy')

insert intotNews(Keyword,Title,Contents,Remark) values ('csdn','sdax','x','xy')

insert intotNews(Keyword,Title,Contents,Remark) values ('csdns','sdax','x','xy')

insert intotNews(Keyword,Title,Contents,Remark) values ('a113ns','s3x','3ax','xy')

insert intotNews(Keyword,Title,Contents,Remark) values ('没有','szx','mx','mei')

 

--查询表数据

select*from tNews

--关键字含有a的放在最前面的行，标题含有a的放在次于关键字含有a的行 …

没有含有a的放在最后

依次排序为：Keyword>Title> Contents>Remark

★排序Sql语句：

select NewsId,Keyword,Title,Contents,Remarkfrom tNews

order by

case when Keyword like '%a%' then 1 else 10end,

case when Title like '%a%' then 2 else 10end,

case when Contents like '%a%' then 3 else10 end,

case when Remark like '%a%' then 4 else 10end

★排序后的结果：