[SQL] 依照 in 來排序

// 這裡是用 ,pid, 當作排序依據 charindex(exp1,exp2)會回傳exp1所在的位置,起始值是1 
select pid,name
from temp
where pid in ('p004','p008','p435','p123','p056')
order by charindex(',' + cast(pid as varchar(10) + ',' , ',p004','p008','p435','p123','p056,' ))

//如果排序的對象有空白(不管空白是在字串前或後) 可以用 rtrim() 來Trim掉空白
select rtrim(pid),name
from temp
where pid in ('p004','p008','p435','p123','p056')
order by charindex(',' + rtrim(cast(pid as varchar(10)) + ',' , ',p004','p008','p435','p123','p056,' ))
 

reference:
rtrim()

charindex()