分类统计时候，我们可能经常会碰到这样的需求，每个分类按照一定顺序，取几条数据，然后在一起显示。
这个问题的解决方法，我们通过搜索引擎，可以找到很多中。但是不是SQL语句过于复杂，就是在数据量比较大时候，性能特别成问题。
今天我就碰到这样一个需求。而我自己的解决方案就是SQL过于复杂，或者性能比较差的。为此我在CSDN论坛发了个帖子，看有没有更好的解决方案。
http://topic.csdn.net/u/20080504/14/5c5866c3-8b91-45ef-ab17-f994f88f8e42.html
CSDN的 SQL Server 板块 不愧是高手云集，问题发出不到半小时，就获得了近10种解决方案。经过测试，我把性能最高，且SQL不复杂的方案整理出来。特别感谢 jinjazz 的解答。
问题详细描述如下：
比如，假设我们有下面这样结构的一张表，这张表的数据量非常巨大。
CREATE TABLE table1
(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](128) NOT NULL,
[class] int not null,
[date] datetime not null
)
class 表示分类编号。 分类数不固定， 至少有上千种分类
date 表示该条记录被更新的时间
我们现在想获得每个分类最新被更新的5条记录。
解决方案
select id,name,class,date from(
select id,name,class,date ,row_number() over(partition by class order by date desc)
as rowindex from table1) a
where rowindex <= 5
解决方案简单说明：
这个解决方案的关键就是使用了SQL 2005 的 ROW_NUMBER 这个全新的函数。
ROW_NUMBER ( ) 函数的语法如下：
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
OVER 子句中的 PARTITION BY 将结果集分为多个分区。
OVER 子句中的 ORDER BY 将对 ROW_NUMBER 进行排序。
下面是MSDN的几个简单例子：
以下示例将根据年初至今的销售额，返回 AdventureWorks 中销售人员的 ROW_NUMBER。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName ,
ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS "Row Number" ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
以下示例将返回行号为 50 到 60 的行（包含这两行），并按 OrderDate 进行排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS "RowNumber"
FROM Sales.SalesOrderHeader )
SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;
以下示例说明了如何使用 PARTITION BY 参数。
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName ,
ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number" ,
s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
>>Source Link
>>Blog: joycode
>>Publish Date: 5/6/2008 7:02:32 AM
>>Keywords: number row
Related Posts
>>Google Adjusts Search Box for Longer Queries #
Google recently started to automatically adjust the search box when you enter longer queries. This won"t happen as you type, but once you"re on a search result page and you entered a longer query befo
>>How Much Textual Information Do We Consume Daily? #
digg_url = "http://digg.com/tech_news/How_Much_Textual_Information_Do_We_Consume_Daily"; digg_bgcolor = "#ffffff"; digg_skin = "compact"; In this post we examine how much textual information we cons
>>RuntimeEntity Preview #
之前发过3个相关的文章:AbstractRecord 预告. (关键字 ORM,ActiveRecord,DomainModel)CSPAbstractRecord之Teddy"s KB样式的性能报告.近况:AbstractRecord改名为RuntimeEntity,以及一个MVC框架的预告.因为最近比较忙. 所以一直没有继续做那个工程.现在稍微空闲了些. 所以把之前做的版本整理了一下. 提供下
>>It Takes More Than A Village #
As anyone publishing a web site knows, you have a front row seat when it comes to observing a multitude of visitor behaviors. Yet, when it comes to advertising, we continue to profile site visitors i
>>What Wikipedia Would Look Like in Book Form... #
Wikipedia showcases above image -- the size of English Wikipedia, when printed out -- and writes: Using volumes 25cm high and 5cm thick (some 400 pages), each page having two columns, each columns
>>Google Spreadsheets Adds Hiding, Color Rules #
Google Spreadsheets has several new features, as a Google Docs Guide in the official help group on the subject announced today: Cell colors based on your custom rules (like "text contains: ...").
>>Natural Language Programming? #
Function Show Books (search query) If the database connection as specified in global settings isn"t open, open it. Grab all book records which contain the search query anywhere.
>>Sergey Brin Portrayed #
Mark Malseed, co-author of The Google Story, takes an in-depth look at Google co-founder Sergey Brin and his family, mother Eugenia (NASA research scientist), father Michael (mathematics professor), a
>>Gmail Supporting Shift Key #
Not sure since when Gmail is supporting the Shift key in certain situations, but I find it very useful. The following two examples work in the normal version of Gmail -- not the basic HTML one, but th
>>SQL Server 索引基础知识(9)----Indexing for OR #
我们仍然是通过例子来理解OR运算符的特征 我们仍然使用 http://blog.joycode.com/ghj/archive/2008/01/18/113870.aspx 中的 member 表,这时候,这个表的索引如下: 名字 描述 列 member_corporation_link nonclustered located on PRIMARY corp_no member_ide