1.在SELECTt列表中创建的别名不能在前面的步骤,只能在SELECTt阶段之后的步骤(如ORDER BY阶段)中使用别名。
SELECT YEAR(orderdate) AS orderyar ... ORDER BY orderyear.
2.WITH TIES
指定从基本结果集中返回附加的行,这些行包含与出现在 TOP
n (PERCENT) 行最后的
ORDER
BY
列中的值相同的值。
3.在ORDER BY字句中,也可以指定SELECT列表中结果列的位置序号。
SELECT orderid,customerid FROM dbo.orders ORDER BY 2,1
4.APPLY
APPLY运算符涉及一下两个步骤总的一步或两步(取决于APPLY的类型):
1.A1:把右表表达式应用于左表的行
2.A2:添加外部行。
SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
CROSS APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A;
SELECT C.customerid, C.city, A.orderid
FROM dbo.Customers AS C
OUTER APPLY
(SELECT TOP (2) O.orderid, O.customerid
FROM dbo.Orders AS O
WHERE O.customerid = C.customerid
ORDER BY orderid DESC) AS A;