SQLServer2005中窗口函数.doc

.简介： SQL Server 2005中的窗口函数帮助你迅速查看不同级别的聚合，通过它可以非常方便地累计总数、移动平均值、以及执行其它计算。窗口函数功能非常强大，使用起来也十分容易。可以使用这个技巧立即得到大量统计值。窗口是用户指定的一组行。 开窗函数计算从窗口派生的结果集中各行的值。2.适用范围：排名开窗函数和聚合开窗函数.也就是说窗口函数是结合排名开窗函数或者聚合开窗函数一起使用OVER子句前面必须是排名函数或者是聚合函数3.例题： --建立订单表create?table?SalesOrder(OrderID?int,????????????--订单idOrderQty?decimal(18,2)??--数量)go--插入数据insert?into?SalesOrderselect?1,2.0union?allselect?1,1.0union?allselect?1,3.0union?allselect?2,6.0union?allselect?2,1.1union?allselect?3,8.0union?allselect?3,1.1union?allselect?3,7.0go--查询得如下结果select?*?from?SalesOrdergoOrderID?????OrderQty-----------?------------1???????????2.001???????????1.001???????????3.002???????????6.002???????????1.103???????????8.003???????????1.103???????????7.00 现要求显示汇总总数，每当所占比例，分组汇总数，每单在各组所占比例，要求格式如下： OrderID?OrderQty?汇总???? 每单比例?分组汇总?每单在各组比例1??????????? 2.00???? 29.20??????? 0.0685????? 6.00?????????? 0.33331??????????? 1.00???? 29.20??????? 0.0342????? 6.00?????????? 0.16671??????????? 3.00???? 29.20?????? ?0.1027????? 6.00?????????? 0.50002??????????? 6.00???? 29.20??????? 0.2055????? 7.10?????????? 0.84512??????????? 1.10???? 29.20??????? 0.0377????? 7.10?????????? 0.15493??????????? 8.00???? 29.20??????? 0.2740????? 16.10???????? 0.49693??????????? 1.10???? 29.20??????? 0.0377????? 16.10???????? 0.06833??????????? 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?SalesOrderorder?by?OrderID 窗口函数是sql2005新增加的,下面我们看看在sql2000里面怎么实现上述的结果：sql2000的实现步骤较麻烦,先计算出总数，再分组计算汇总，最后连接得到结果 --sql2000declare?@sum?decimal(18,2)select?@sum=sum(OrderQty)?from?SalesOrder--按OrderID，计算每组的总计，然后插入临时表select?OrderID,sum(OrderQty)?as?suinto?#tfrom?SalesOrdergroup?by?OrderID--连接临时表，得到