[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进行分区. 其实就是把操作窗口(可操作的数据行集合)进行分组, 并匹配出符合分组参数的行集合. 那么我们来看例子.
某表如下, 订单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 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,88union 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
最新文章
- spring in action 4th --- quick start
- ViewPager +Fragment 滑动游标
- PHP向mysql中插入数据的方法
- HOWTO - 压缩封装的Setup.exe(纯MSI)安装包获取运行Log
- Linux每天定时重启Tomcat服务
- How ADB works
- Hibernate逍遥游记-第12章 映射值类型集合-003映射List(<;list-index>;)
- poj 3318 Matrix Multiplication 随机化算法
- Java笔记(十四)&hellip;&hellip;抽象类与接口
- 《University Calculus》-chaper13-多重积分-二重积分的计算
- laravel的模块化是如何实现的
- (7)基于hadoop的简单网盘应用实现3
- 小老虎的微信公众号:iITObserve
- Android studio 中添加依赖model时依赖所需的准备
- cd4与cd8比值的意义
- date格式互转
- 推荐:全新Java开发思维导图
- 解决hash冲突的三个方法(转)
- ORACLE 实用案列
- 【Unity/C#】DateTime时间字符串,月份用英文显示
热门文章
- 【u248】交通序列号
- 大数据(十四) - Storm
- ";网络适配器本地连接没有有效ip地址配置";错误的解决办法
- ios开发网络学习六:设置队列请求与RunLoop
- POJ - 2286 - The Rotation Game (IDA*)
- [Angular Unit Testing] Testing Services with dependencies
- Android的NDK开发(5)————Android JNI层实现文件的read、write与seek操作
- 【codeforces 754A】Lesha and array splitting
- Git Push问题remote: hooks/update:10 undefined method &;#39;require_relative&;#39; for main:Object(NomethodError)
- [Ramda] Refactor to Point Free Functions with Ramda using compose and converge