探讨SQLServer2005的评价函数

　一、 简介

　　在2005年11月份，微软发行了三种新产品系列：Visual Studio 2005，SQL Server 2005和.NET框架2.0(它包含ASP.NET 2.0)。SQL Server 2005是微软自从其上一个重要发行版本SQL Server 2000以来最新版本的数据库平台。在过往五年的发展中，SQL Server中参加了大批的新特点，所有这些新内容都被总结到微软网站的一篇文章《What's New in SQL Server 2005?》中。应用SQL Server 2005作为后端数据库构建基于web利用程序的开发者很可能会对这些新特点抱有浓重的爱好，这些新特点包含新的T-SQL改良，更好的Visual Studio集成，与CLR/.NET框架的集成，以及SQL Server 2005 Management Studio利用程序（它是SQL Server 2000的企业治理器的一个更为"平滑"的版本）。

　　与以前的SQL Server 2000相比， 2005中的T-SQL改良使得编写某些类型的查询极为轻易。在SQL Server 2005中，T-SQL语法更为精练、可读和易于懂得。

　　在本文中，我们将专门探讨SQL Server 2005的评价函数，它们大大简化了对查询成果进行评价的过程。

　　二、 数据模型和评价成果基础

　　在我们分析如何应用普通查询模式之前，让我们首先创立一个能够运行这些查询的数据模型。在本文中，我应用SQL Server 2005 Express版本来实现我的演示，并且包含了一个数据库和一个ASP.NET 2.0网站（请参考本文相应的完整源码。就象Visual Studio一样，SQL Server发行中也一同加杂了其它一些不同的版本。其中，Express版本是一个针对业余爱好者、学生等群体的免费版本。假如你下载和安装Visual Web Developer（Visual Studio针对web开发者的Express版本），那么你可以选择一同安装SQL Server 2005 Express版本）。

　　对于本文中的示例，我们将应用一个含有产品、销售职员（雇员）、顾客和订单信息的数据库。我们应用五个表来建模：Customers，Employees，Products，Orders和OrderItems。其中，Customers，Employees和Products表分辨包含每一个顾客，雇员和产品信息的行记录数据。每当一个顾客进行购置运动，一条新记录被添加到Orders表中，其中的信息唆使该顾客实现了购置、该雇员进行的这一销售运动及订单的日期。其中，OrderItems映射订单中的每一件产品，产品的数目和价格总值(假定较大的购置量可以打折)。下图展现了这些表（及字段）以及它们之间的关系。

　
如图所展现的，这个OrderItems在Orders和Products表之间建立一个对多对的连接。
ProductID Name Price PriceRank 8 Desk 495.0000 1 10 Executive Chair 295.0000 2 9 Chair 125.0000 3 5 Mouse 14.9500 4 6 Mousepad 9.9900 5 11 Scissors 8.5000 6 4 Stapler 7.9500 7 3 Binder 1.9500 8 ...
　　默认情况下，这个ROW_NUMBER函数把一个增量值（逐次加1）赋给成果集中的每一个记录。借助于可选的partition参数，无论何时分区（partitioning）列值产生变更，你都可以让ROW_NUMBER函数重新盘算行数。为了阐明这个标题，我应用如下查询语法创立了一个视图vwTotalAmountBilledPerOrder，它将返回每一个OrderID和该订购的总订单数：

SELECT OrderID，SUM(AmountBilled) AS TotalOrderAmountFROM OrderItemsGROUP BY OrderID
　　这条语句将返回OrderItems表中每一个唯一的订单，还有相应于该订单的AmountBilled值的和。借助于这个视图，我们可以应用ROW_NUMBER方法来按最大花钱数来评价这些订单，如下所示：

SELECT c.Name，o.DateOrdered，tab.TotalOrderAmount， ROW_NUMBER() OVER (ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID
　　这个语句将返回如下表所示的成果：

Name DateOrdered TotalOrderAmount BestCustomer Bob 12/1/2005 12649.9900 1 Darren 1/2/2006 620.0000 2 Bob 12/19/2005 265.8500 3 Tito 12/22/2005 14.9500 4 Bruce 1/5/2006 14.9500 5 Tito 12/18/2005 12.4400 6 Bruce 1/4/2006 9.9900 7 Lee Ann 1/3/2006 8.5000 8 ...
　　留心，某些顾客多次呈现在这个列表中（如Bob，Tito和Bruce）。也许有时，我们不是想观看以销售量排序的所有订单，而更想看到每一个顾客的最高订单量。为此，我们可以通过应用ROW_NUMBER函数中的PARTITION BY子句达到这一目标，如下所示：

SELECT c.Name， o.DateOrdered， tab.TotalOrderAmount， ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomerFROM vwTotalAmountBilledPerOrder AS tab INNER JOIN Orders AS o ON o.OrderID = tab.OrderID INNER JOIN Customers AS c ON c.CustomerID = o.CustomerID
　　这个语句将返回如下表所示的成果：

Name DateOrdered TotalOrderAmount BestCustomer Bob 12/1/2005 12649.9900 1 Bob 12/19/2005 265.8500 2 Tito 12/22/2005 14.9500 1 Tito 12/18/2005 12.4400 2 Darren 1/2/2006 620.0000 1 Bruce 1/5/2006 14.9500 1 Bruce 1/4/2006 9.9900 2 Lee Ann 1/3/2006 8.5000 1 ...
　　留心，尽管这些成果非常不错；但是，你却不能在WHERE语句中应用ROW_NUMBER()函数(或任何其它的评价函数)。也就是说，你可能想要说，"把按价格评价第5到第8名的产品列出"。为此，你需要应用一个派生的表或视图。例如，你可以把上面的查询放到一个视图vwPriceRankedProducts中，然后应用如下查询返回第5到第8个排名的产品：