SQL 语句技巧--排名函数的使用实例

   今天看到一篇文章，关于讨论如何优化语句，原文如下：

       作者总结贴： http://blog.csdn.net/wangd1121/archive/2008/06/23/2579435.aspx

       网友讨论贴： http://topic.csdn.net/u/20080504/14/5c5866c3-8b91-45ef-ab17-f994f88f8e42.html 

 

   问题描述：

比如，假设我们有下面这样结构的一张表，这张表的数据量非常巨大。
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条记录。

 

   解决的SQL语句：

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

 

   的确是一个很好的解决办法。

      注意：OVER 子句中的 PARTITION BY 将结果集分为多个分区，这里的partition by class是按照类型分区，当然是有多少个类型分多少个区。

 

    当然作者讨论没有涉及到后续的性能优化讨论，因为表的数据量比较大情况下，如何优化该查询。如果不建立任何索引和处理，以上的执行语句是全表扫描，速度很慢的。

   由于没有上面的数据量，我们从另一个表中模拟这个实现：

    表[zping.com]大约有数据70多万数据，有字段id,operator,operatedate,remark等字段信息，其中operator有两三百个不同值，和上面的执行意义一样：

select * from 
(select operator , operatedate,row_number() over 
(partition by operator order by operatedate ) rn 
from  dbo.[zping.com]
) t where rn<=5

 

  如果没有建立索引，上述查询速度很慢,这时要如果在operator和operatedate分别建立索引也很慢，建立（operatedate，operator）索引也比较慢，因为要排序，这时建立复合索引（operator，operatedate).