此外根据测试 MAX,MIN,AVG 之类的统计函数还是可以在分析函数中应用的，不过和 Oracle 的处理有点不一样，好像无法应用 OVER 中的 ORDER BY 子句，大概是不够成熟导致的。
SQLServer2005 提供的函数也远不如mmic Oracle10G 来的丰富，类似 STDDEV 之类的统计分析函数仅在 MDX 中提供;不过其他一些数据分布的分析函数可以采用变通的方式予以实现。
经过测试，以下代码 可以在 SQLServer2005 和 Oracle10g 中同时运行，结果顺序有少许不一致。
-- 创建相关测试表
CREATE TABLE Sales
(
EmpID VARCHAR(10) NOT NULL PRIMARY KEY,
MgrID VARCHAR(10) NOT NULL,
Qty INT NOT NULL
);
INSERT INTO Sales VALUES('A','Z',300);
INSERT INTO Sales VALUES('B','X',100);
INSERT INTO Sales VALUES('C','X',200);
INSERT INTO Sales VALUES('D','Y',200);
INSERT INTO Sales VALUES('E','Z',250);
INSERT INTO Sales VALUES('F','Z',300);
INSERT INTO Sales VALUES('G','X',100);
INSERT INTO Sales VALUES('H','Y',150);
INSERT INTO Sales VALUES('I','X',250);
INSERT INTO Sales VALUES('J','Z',100);
INSERT INTO Sales VALUES('K','Y',250);
--ROW_NUMBER 函数
SELECT EmpID,MgrID,Qty,
ROW_NUMBER() OVER(ORDER BY Qty) AS RowNum_BY_Qty,
ROW_NUMBER() OVER(ORDER BY Qty,EmpID) AS RowNum_BY_EmpIDQty,
ROW_NUMBER() OVER(PARTITION BY MgrID ORDER BY Qty,EmpID) AS RowNum_BY_MgrID_EmpIDQty
FROM Sales
ORDER BY Qty
-- 等效语句
SELECT EmpID,Qty,
(SELECT COUNT(*)
FROM Sales S2
WHERE S2.Qty OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID)) RowNum FROM Sales S1 ORDER BY Qty,EmpID -- 等效语句 SELECT MgrID,EmpID,Qty, (SELECT COUNT(*) FROM Sales S2 WHERE S2.MgrID=S1.MgrID AND (S2.Qty OR (S2.Qty=S1.Qty AND S2.EmpID<=S1.EmpID))) RowNum FROM Sales S1 ORDER BY MgrID,Qty,EmpID --RANK 函数和 DENSE_RANK 函数 SELECT EmpID,MgrID,Qty, RANK() OVER(ORDER BY Qty) AS Rank, DENSE_RANK() OVER(ORDER BY Qty) AS Dense_Rank, RANK() OVER(PARTITION BY MgrID ORDER BY Qty) AS Rank_Partition, DENSE_RANK() OVER(PARTITION BY MgrID ORDER BY Qty) AS Dense_Rank_Partition FROM Sales ORDER BY Qty --NTILE 函数 SELECT EmpID,MgrID,Qty, NTILE(3) OVER(ORDER BY Qty) AS Ntile_BY_Qty, NTILE(3) OVER(ORDER BY Qty,EmpID) AS Ntile_BY_EmpIDQty, NTILE(3) OVER(PARTITION BY MgrID ORDER BY Qty,EmpID) AS Ntile_BY_MgrID_EmpIDQty FROM Sales ORDER BY MgrID,Qty 关于这些函数的实现原理和 Oracle 是完全一致的，在此不多做叙述
"
