SQL的魅力 -- Order by 的灵活用法

时间：2010-11-08 来源：JasperZhu

普通列排序

SELECT LastName, FirstName FROM Person.Person
WHERE 1=1
ORDER BY FirstName ASC, LastName DESC ;

计算列排序

SELECT SID, convert(varchar(10),RequestTime,120) as myTime FROM Site_Visit_Detail
WHERE 1=1
ORDER BY myTime DESC ;

实际上还有很多更灵活，更实用的用法，在实际的需求中会经常遇到，如：

在order by 中使用Case

select * from YourTable
order by case At_Id when 102 then Art_Id when 101 then Art_Order end desc

在order by 中使用Case—9.0及以后的版本. 可以用作在存储过程中将排序字段作为传入的参数

declare @orderby varchar(100)
set @orderby='Art_Name'
SELECT ROW_NUMBER() OVER (ORDER BY (case @orderby when 'Art_Name' then Art_Name end) desc, Art_Order)AS Row,*
from
(
    select Art_ID,Art_Name,Art_Source,Art_Order
    from v_Article_Show
    where art_id>3000
) as myTempTable1

将搜索结果按指定的字段值，如ID，顺序呈现

select * from article
where art_id in (106,102,103,104,105)
order by charindex(rtrim(art_id),'106,104,105,103,102')