开窗函数 Row_Number partition by 分类排序

背景, 目前在维护老项目, 但其中有bug.

原先的逻辑在对某个数据的分类编号时不对,导致结果不准确.

解决方案: 用开窗函数 row_number partition by 某个被分类的数据字段, 然后 针对时间戳做个排序

因为在办公室不方便弄, 只好回家自己凭记忆,把主要的点列出来.

以下是主要的表结构:

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblFeed]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblFeed](
	[key] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tblFeed_key]  DEFAULT (newid()),
	[type] [int] NULL,
	[content] [varchar](1000) NULL,
	[createdStamp] [datetime] NOT NULL CONSTRAINT [DF_tblFeed_createdStamp]  DEFAULT (getdate()),
	[lastUpdatedStamp] [datetime] NOT NULL CONSTRAINT [DF_tblFeed_lastUpdatedStamp]  DEFAULT (getdate()),
	[parentkey] [uniqueidentifier] NULL,
	[ownerkey] [uniqueidentifier] NULL,
	[state] [int] NULL
) ON [PRIMARY]
END

主要涉及查询的sql脚本为:

SELECT a.* into #tmpFeed FROM tblFeed a
inner join tblFeed b
	on a.[parentkey]=b.[key]
    and a.ownerKey='B588D659-CF0F-4A28-AD2B-3D9B9FAFC357'
    and b.ownerkey='B588D659-CF0F-4A28-AD2B-3D9B9FAFC357'
    and a.type=1
    and b.type=0

 --select * from #tmpFeed
 -- alter table #tmpFeed add RowNumber int
 -- alter table #tmpFeed drop column RowNumber  


declare @EachCommentCount int
set @EachCommentCount=3

select * from
(
	SELECT *, Row_Number() 
    OVER (partition by parentkey ORDER BY lastupdatedstamp desc) EachCommentIndex 
	FROM #tmpFeed
) a
where EachCommentIndex <= @EachCommentCount

drop table #tmpFeed

初始化数据截图:

查询结果截图:

请看上图的EachCommentIndex

(结束)