SQL按指定文字顺序进行排序(中文或数字等)

在有些情况下我们需要按指定顺序输出数据，比如选择了ID in(3,1,2,5,4)我们希望按这个3,1,2,5,4的顺序输出，这样只使用order by ID是无法实现的，

但是我们可以使用order by charindex(','+convert(varchar,ID)+',',',3,1,2,5,4,')的方法来实现这个目的。举例如下：

Create Table info(

ID int identity(1,1) not null,

title varchar(100) not null

)

insert into info(title) values('aa')
insert into info(title) values ('bb')
insert into info(title)values('cc')
insert into info(title) values('dd')
insert into info(title)values('ee')

/*方法一：*/
select id,title

from info

where id in ('3','1','2','5','4')

order by charindex(','+convert(varchar,ID)+',' , ',3,1,2,5,4,')

/*方法二：*/
select id,title

from info

where id in ('3','1','2','5','4')
--where id in ('1','2','3','4','5')

order by  CHARINDEX(RTRIM(CAST(id as NCHAR)), ',3,1,2,5,4,')

/*其它示例*/
select prx_class,prx_name,isnull(sum(sunMen),0) as sunMen,isnull(sum(sumMoney),0)  as sumMoney  from 
(   select prx_class,prx_code,prx_name,isnull(sum(money),0) as sunMen, case when prx_code='项目冲减' then isnull(sum(num),0) * -1  else isnull(sum(num),0) end   as sumMoney 
    from receive_paybill   
    WHERE the_date >='2014/6/1 0:00:00' AND the_date<='2014/6/10 0:00:00'  and prx_code is not null and prx_code<>'' and substoreid='004' 
    group by prx_code,prx_name,prx_class 
) as tb 
group by prx_name,prx_class
--order by CHARINDEX(RTRIM(CAST(prx_class as NCHAR)),',小商品,钟点房费,会员卡费,房费,')  desc
order by charindex(','+prx_class+',',',小商品,钟点房费,会员卡费,房费,') desc

 

转载于:https://www.cnblogs.com/elves/p/3818124.html