Sql Server2005 Transact-SQL 新兵器学习总结之-窗口函数(OVER)-白红宇

系列文章导航

Sql Server2005 Transact-SQL 新兵器学习总结之-总结

免费美女视频聊天,多人视频会议功能加强版本(Fms3和Flex开发(附源码))

1.简介：

SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。

窗口函数功能非常强大，使用起来也十分容易。可以使用这个技巧立即得到大量统计值。

窗口是用户指定的一组行。开窗函数计算从窗口派生的结果集中各行的值。

2.适用范围：

排名开窗函数和聚合开窗函数.

也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用

OVER子句前面必须是排名函数或者是聚合函数

3.例题：

复制代码

--建立订单表

create table SalesOrder(

OrderID int, --订单id

OrderQty decimal(18,2) --数量

)

--插入数据

insert into SalesOrder

select 1,2.0

union all

select 1,1.0

union all

select 1,3.0

union all

select 2,6.0

union all

select 2,1.1

union all

select 3,8.0

union all

select 3,1.1

union all

select 3,7.0

--查询得如下结果

select * from SalesOrder

OrderID OrderQty

----------- ------------

1 2.00

1 1.00

1 3.00

2 6.00

2 1.10

3 8.00

3 1.10

3 7.00

复制代码

现要求显示汇总总数，每当所占比例，分组汇总数，每单在各组所占比例，要求格式如下：

OrderID OrderQty 汇总每单比例分组汇总每单在各组比例

1 2.00 29.20 0.0685 6.00 0.3333

1 1.00 29.20 0.0342 6.00 0.1667

1 3.00 29.20 0.1027 6.00 0.5000

2 6.00 29.20 0.2055 7.10 0.8451

2 1.10 29.20 0.0377 7.10 0.1549

3 8.00 29.20 0.2740 16.10 0.4969

3 1.10 29.20 0.0377 16.10 0.0683

3 7.00 29.20 0.2397 16.10 0.4348

复制代码

--利用窗口函数和聚合开窗函数,可以很快实现上述要求

select OrderID,OrderQty,

sum(OrderQty) over() as [汇总],

convert(decimal(18,4), OrderQty/sum(OrderQty) over() ) as [每单所占比例],

sum(OrderQty) over(PARTITION BY OrderID) as [分组汇总],

convert(decimal(18,4),OrderQty/sum(OrderQty) over(PARTITION BY OrderID)) as [每单在各组所占比例]

from SalesOrder

order by OrderID

复制代码

窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果：

sql2000的实现步骤较麻烦,先计算出总数，再分组计算汇总，最后连接得到结果

复制代码

--sql2000

declare @sum decimal(18,2)

select @sum=sum(OrderQty)

from SalesOrder

--按OrderID，计算每组的总计，然后插入临时表

select OrderID,sum(OrderQty) as su

into #t

from SalesOrder

group by OrderID

--连接临时表，得到结果

select s.OrderID,s.OrderQty,

@sum as [汇总],

convert(decimal(18,4),s.OrderQty/@sum) as [每单所占比例],

t.su as [分组汇总],

convert(decimal(18,4),s.OrderQty/t.su) as [每单在各组所占比例]

from SalesOrder s join #t t

on t.OrderID=s.OrderID

order by s.OrderID

drop table #t

复制代码

上面演示的都是窗口函数与聚合开窗函数的使用，它与排名开窗函数请看下面例题:

复制代码

--与排名开窗函数使用

select OrderID,OrderQty,

rank() over(PARTITION BY orderid order by OrderQty ) as [分组排名],

rank() over(order by OrderQty ) as [排名]

from SalesOrder

order by orderid asc

--查询得如下结果

OrderID OrderQty 分组排名排名

1 2.00 2 4

1 3.00 3 5

1 1.00 1 1

2 1.10 1 2

2 6.00 2 6

3 7.00 2 7

3 8.00 3 8

3 1.10 1 2

复制代码

与排名开窗函数的详细使用请看我的另一篇文章

收藏与分享

收藏到QQ书签添加到百度搜藏添加到百度搜藏添加到雅虎收藏分享到饭否收藏到就喜欢网络收藏夹

RSS订阅我什么是RSS?

feedsky http://wap.feedsky.com/aierongrss E-mail

订阅到雅蛙使用RSS邮天下订阅订阅到有道阅读

订阅到抓虾鲜果阅读器订阅图标 Add to Google

訂閱 Bloglines 哪吒提醒 Subscribe in NewsGator Online

东莞.net俱乐部

东莞.net俱乐部欢迎您的加入

我的系列文章

A.Sql Server2005 Transact-SQL 新兵器学习

B.MCAD学习

C.代码阅读总结

D.ASP.NET状态管理

E.DB(数据库)

F.WAP

G.WinForm

H.Flex

我的好文推荐

FlexAir开源版-全球免费多人视频聊天室,免费网络远程多人视频会议系统((Flex,Fms3联合开发))<视频聊天,会议开发实例8>

Sql Server2005 Transact-SQL 新兵器学习总结之-总结

MS SQL数据库备份和恢复存储过程(加强版本)

sql server中分布式查询随笔(链接服务器(sp_addlinkedserver)和远程登录映射(sp_addlinkedsrvlogin)使用小总结)

ASP.NET2.0国际化/本地化应用程序的实现总结(多语言,多文化页面的实现)

WAP开发资料站(最新更新)

自定义格式字符串随笔 (IFormattable,IFormatProvider,ICustomFormatter三接口的实现)

Mcad学习笔记之异步编程(AsyncCallback 委托,IAsyncResult接口,BeginInvoke方法,EndInvoke方法的使用小总结)

Mcad学习笔记之通过反射调用類的方法,屬性,字段,索引器(2種方法)

Mcad学习笔记之序列化(2进制和Soap序列化)

Mcad学习笔记之委托再理解(delegate的构造器,BeginInvoke,EndInvoke,Invoke4个方法的探讨)

ASP.NET状态管理之一（概括篇）

Flex,Fms学习笔记

本文转自aierong博客园博客，原文链接：http://www.cnblogs.com/aierong/archive/2008/08/26/1273890.html，如需转载请自行联系原作者