在视图中添加标识列（源表数据聚合）

/*

视图显示源表按某种规则聚合的数据，为分页显示，需要在视图中添加int的列标识 .
示例中源表数据按日期聚合，字段包括varchar,datetime,int三种类型.

*/

/*基础表结构*/

/****** Object:  Table [dbo].[T_UsageTrack]    Script Date: 2005-09-29  ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_UsageTrack]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[T_UsageTrack]
GO
CREATE TABLE [dbo].[T_UsageTrack] (
 [RID] int IDENTITY(1, 1) NOT NULL PRIMARY KEY  CLUSTERED,
 [VisiteDate] [datetime]  NOT NULL DEFAULT(Convert(varchar(10),getDate(),21)),
 [ArticleID] varchar(10)  NOT NULL ,
 [ArticleTitle] varchar(200)  NOT NULL ,
 [Visitor] varchar(100)  NOT NULL ,
 [VisiteTime] varchar(8)  NOT NULL DEFAULT(Convert(varchar(8),getDate(),8))
)

GO

/*创建视图并添加标识列RID*/

/****** Object:  View dbo.V_UsageTrack    Script Date: 2005-10-10 下午 15:35:02 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[V_UsageTrack]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[V_UsageTrack]
GO
CREATE VIEW [dbo].[V_UsageTrack]  AS
 Select TOP 100 PERCENT
     RID=(Select Count(1) From
       (Select VisiteDate, ArticleID
    From T_UsageTrack
   Where VisiteDate<a.VisiteDate
   Or (VisiteDate=a.VisiteDate And (ArticleID<a.ArticleID
           Or (ArticleID=A.ArticleID And ArticleTitle<=A.ArticleTitle)
       )
   )
   Group By VisiteDate, ArticleID, ArticleTitle
      )
            AA ), VisiteDate, ArticleID, ArticleTitle, Count(RID) As Num
 From T_UsageTrack a
 Group By VisiteDate, ArticleID,ArticleTitle
 Order By VisiteDate Desc

GO