在SQL中通常Order by是这样用的:
普通列排序
SELECT LastName, FirstName FROM Person.Person
WHERE 1=1
ORDER BY FirstName ASC, LastName DESC ;
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 ;
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 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
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')
where art_id in (106,102,103,104,105)
order by charindex(rtrim(art_id),'106,104,105,103,102')