TSql order by Clause 排序列的多种写法

Order by 子句后面有两种类型的字段：第一种是：column_name, column_alias 和 nonnegative integer，第二种是Column Expression。

order_by_expression                                

Specifies a column or expression on which to sort the query result set. A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the column in the select list.

Multiple sort columns can be specified. Column names must be unique. The sequence of the sort columns in the ORDER BY clause defines the organization of the sorted result set. That is, the result set is sorted by the first column and then that ordered list is sorted by the second column, and so on.

The column names referenced in the ORDER BY clause must correspond to either a column in the select list or to a column defined in a table specified in the FROM clause without any ambiguities.

示例

1，创建示例表数据

CREATE TABLE [dbo].[dt_test]
(
    [id] [int] NULL,
    [code] [int] NULL,
    [name] [varchar](10) NULL
)

2，使用column name

select id,code,name 
from dbo.dt_test dt with(nolock)
order by dt.id

3，使用Column Alias，order by子句中能够使用Alias的原因是，Sql Server Engine先执行select clause，后执行order by clause。

select id as OrderID,code,name 
from dbo.dt_test dt with(nolock)
order by OrderID

4，使用column order来进行排序，即使用一个正整数来表Select clause中column的顺序来进行排序

select id ,code,name 
from dbo.dt_test dt with(nolock)
order by 1

5，使用column expression来进行排序，执行顺序是先计算Column Expressino的值，然后对查询的结果进行排序。

select id ,code,name 
from dbo.dt_test dt with(nolock)
order by id+code

select id ,code,name 
from dbo.dt_test dt with(nolock)
order by id%3

原文：http://www.cnblogs.com/ljhdo/p/4883198.html