首先, 我们确定Sql语句执行大致流程

• FROM   =>> WHERE =>> GROUP BY =>> HAVING =>> SELECT ==>

•             { Over

•                Distinct

•                Top} ==>

• ORDER BY

其中比较难一点的就是Over窗口函数了.

Over单独配合聚合函数

SELECT * FROM Sales.OrderValues;

有表如图1

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

FROM Sales.OrderValues;

图2

对比上图, 发现除了增加了两列外, 没有改变排序.

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue,

  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  /*,AVG(val) over(PARTITION BY orderdate) as custAvgValue*/

FROM Sales.OrderValues;

图3

对比图1. 发现, Partition By 不仅让sum聚合函数只计算CustID区域的val. 并且使得结果按custid排序

SELECT orderid, custid, val,orderdate

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

  ,SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  ,AVG(val) over(PARTITION BY orderdate) as custAvgValue

FROM Sales.OrderValues;

图4

对比图3, 发现如果再增加一个有partitionby子句的窗口函数, 结果按照后一个(这里是orderdate)排序

SELECT orderid, custid, val,orderdate,ROW_NUMBER() over(order by val desc)

  ,SUM(val) OVER() AS totalvalue,

  AVG(val) over() as avgvalue

  ,SUM(val) OVER(PARTITION BY custid) AS custtotalvalue

  /*,AVG(val) over(PARTITION BY orderdate) as custAvgValue*/

  FROM Sales.OrderValues;

结果同图4一样. 虽然增加了一列RowIndex列, 但是结果依然是按照custid排序, 除非将那一句放到后面.

如果排序函数中使用了partition by子句. 那么, 返回的结果还是以partition by子句的字段升序.

 

当然, 以上结果是在没有order by排序的前提下. 如果最外层有order by, 那么还是按order by 排序.

with ties

WITH TIES

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。

注意：返回的记录关联顺序是任意的。ORDER BY 不影响此规则

来源：MSDN，http://msdn.microsoft.com/zh-cn/library/ms189463.aspx

通俗解释

如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n+1…n+k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n+1、…、n+k也返回。n+1、…、n+k就是额外的返回值。

举例说明

declare @tb table(tname varchar(10), score int)

insert into @tb select 'a',80

insert into @tb select 'b',80

insert into @tb select 'c',70

insert into @tb select 'd',60

insert into @tb select 'e',50

insert into @tb select 'f',40

insert into @tb select 'g',40

insert into @tb select 'h',30

insert into @tb select 'i',80

insert into @tb select 'j',70

select top 4 * from @tb order by score desc

返回结果如下.  但是我们发现 j的分数也是70, 这个时候我们需要统计j的时候, with ties就拍上用场了.

select top 4 with ties * from @tb order by score desc

OVER子句用于为行为定义一个窗口（windows），以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。例如，聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口，所以这些函数也称之为开窗函数。

简单的理解, 就是over提供了任意的聚合. 在一般情况下, 如果我们要用到聚合函数, 需要先使用groupby分组. 用了over之后, 某些情况就简单很多.

如果over()里面没有子句, 那么, over子句前面的聚合函数的操作窗口(可操作的数据行集合)为该次查询返回的所有行.  over子句提供partition by进行分区. 其实就是把操作窗口(可操作的数据行集合)进行分组, 并匹配出符合分组参数的行集合. 那么我们来看例子.

某表如下, 订单Id, 所属客户, 和价值val.

我们想在后面加上几列数据以更好地支持我们的显示系统. totalValue(查询的所有价值之和), avgValue(查询的所有价值平均数),custAvgValue(某客户的订单价值之和),custTotalValue(某客户的订单价值平均)

期望得到如图

declare @dt datetime;set @dt=GETDATE()

SELECT orderid, custid, val,

  SUM(val) OVER() AS totalvalue,--所有行作为操作值

  AVG(val) over() as avgvalue,--所有行作为操作值

  AVG(val) over(PARTITION BY custid) as custAvgValue,--按客户ID进行分区, 并自动抽取出符合当前行的分区参数(这里是custid)的数据行

  SUM(val) OVER(PARTITION BY custid) AS custtotalvalue--按客户ID进行分区, 并自动抽取出符合当前行的分区参数(这里是custid)的数据行

FROM Sales.OrderValues;

--同样的效果, 用老式的方式进行查询

select DATEDIFF(ms,@dt,getdate())

set @dt=GETDATE()

SELECT orderid, custid, val,

  (select SUM(val) from sales.OrderValues) AS totalvalue,

  (select avg(val) from sales.OrderValues) as avgvalue,

  (select avg(val) from sales.OrderValues where custid=s.custid) as custAvgValue,

  (select sum(val) from sales.OrderValues where custid=s.custid) AS custtotalvalue

FROM Sales.OrderValues s;

select DATEDIFF(ms,@dt,getdate())

--表的数据为800多行. 第一种方式13-20ms. 第二种方式耗时40-50ms. 大数据量可见效率之差

partition by 和 order by连用.

sqlserver2005之后, 有一个排序函数, row_number() over(order by XX).

准备脚本

declare @Student table  --学生成绩表

(

 id int,  --主键

 Grade int, --班级

 Score int --分数

)

insert @Student 

    select 1,1,88

union all select 2,1,66

union all select 3,1,75

union all select 4,2,30

union all select 5,2,70

union all select 6,2,80

union all select 7,2,60

union all select 8,3,90

union all select 9,3,70

union all select 10,3,80

--不分年级按学生成绩排名

select *,ROW_NUMBER() over(order by Score desc) as Sequence from @Student

--分年级按学生成绩排名,并取得年级平均成绩

select *, ROW_NUMBER() over(partition by grade order by score desc) as sequence, 

        AVG(score*1.0) over(partition by grade) gradeAvgScore from @Student

 

关于over子句和其他排名函数的使用请参考, 相当不错哦http://www.cnblogs.com/tylerdonet/archive/2011/07/08/2101384.html