[Sql Server 2008 基础] With Ties. Over()子句
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',80insert into @tb select 'b',80insert into @tb select 'c',70insert into @tb select 'd',60insert into @tb select 'e',50insert into @tb select 'f',40insert into @tb select 'g',40insert into @tb select 'h',30insert into @tb select 'i',80insert into @tb select 'j',70select 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子句
OVER子句用于为行为定义一个窗口(windows),以便进行特定的运算。可以把行的窗口简单地认为是运算将要操作的一个行的集合。例如,聚合函数和排名函数都是可以支持OVER子句的运算类型。由于OVER子句为这些函数提供了一个行的窗口,所以这些函数也称之为开窗函数。
简单的理解, 就是over提供了任意的聚合. 在一般情况下, 如果我们要用到聚合函数, 需要先使用groupby分组. 用了over之后, 某些情况就简单很多.
如果over()里面没有子句, 那么, over子句前面的聚合函数的操作窗口(可操作的数据行集合)为该次查询返回的所有行. over子句提供partition by进行分区. 其实就是把操作窗口(可操作的数据行集合)进行分组, 并匹配出符合分组参数的行集合. 那么我们来看例子.
我们想在后面加上几列数据以更好地支持我们的显示系统. 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 custtotalvalueFROM 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 @Studentselect 1,1,88
union all select 2,1,66union all select 3,1,75union all select 4,2,30union all select 5,2,70union all select 6,2,80union all select 7,2,60union all select 8,3,90union all select 9,3,70union 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