cs_schema_patch_20050421.sql

来自「解压即可使用」· SQL 代码 · 共 34 行

ALTER TABLE [dbo].[cs_Post_Categories] ADD
	[TotalThreads] [int] NOT NULL default(0) ,
	[MostRecentPostDate] [datetime] NULL ,
	[TotalSubThreads] [int] NOT NULL default(0),
	[MostRecentSubPostDate] [datetime] NULL 
GO

declare @UpCategoryID int
declare @UpPath nvarchar(256)
DECLARE Categories_Cursor CURSOR FOR
	SELECT CategoryID FROM cs_Post_Categories

OPEN Categories_Cursor
FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID

WHILE @@FETCH_STATUS = 0
BEGIN

	set @UpPath = (select Path from cs_Post_Categories where CategoryID = @UpCategoryID)

	UPDATE cs_Post_Categories SET
		TotalThreads = (SELECT COUNT(PIC.PostID) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
		TotalSubThreads = (SELECT COUNT(P.PostID) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID),
		MostRecentPostDate = (SELECT MAX(PostDate) FROM cs_Posts_InCategories PIC INNER JOIN cs_Posts jP ON (jP.PostID = PIC.PostID AND jP.PostID = jP.ParentID) WHERE PIC.CategoryID = @UpCategoryID),
		MostRecentSubPostDate = (SELECT MAX(PostDate) FROM cs_Posts P INNER JOIN cs_Post_Categories jC ON (jC.CategoryID = @UpCategoryID OR jC.Path LIKE @UpPath + convert(nvarchar, @UpCategoryID) + '/%') INNER JOIN cs_Posts_InCategories jPIC ON (jPIC.CategoryID = jC.CategoryID) WHERE P.PostID = P.ParentID AND P.PostID = jPIC.PostID)
	WHERE CategoryID = @UpCategoryID

	FETCH NEXT FROM Categories_Cursor INTO @UpCategoryID
END

CLOSE Categories_Cursor
DEALLOCATE Categories_Cursor
GO