每个分类取最新的几条的SQL实现

来源:互联网 发布:软件编码标准 编辑:程序博客网 时间:2024/05/07 11:19

分类统计时候,我们可能经常会碰到这样的需求,每个分类按照一定顺序,取几条数据,然后在一起显示。

这个问题的解决方法,我们通过搜索引擎,可以找到很多中。但是不是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;