mysql向下递归分页_存储过程递归查询及分页示例

数据表【CN_New_Category】截图：

存储过程：

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        ZHAOWEI

-- Create date: 20120410

-- Description:    选取新闻中心所有类别下的数据

-- =============================================

--exec CN_New_GetAllByCategoryID 20,10,0

alter PROCEDURE CN_New_GetAllByCategoryID

(

@CategoryID        int,

@PageSize        INT,

@PageIndex        INT

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @strStartRow    NVARCHAR(50)

DECLARE @strEndRow        NVARCHAR(50)

declare @RecordTable table

(

[id] int

,[categoryid] int

,[title] nvarchar(500)

,[titlecolor] varchar(100)

,[isbold] int

,[content] ntext

,[contentpagecount] int

,[titlelink] varchar(255)

,[image] varchar(50)

,[imagelink] varchar(150)

,[tempstate] int

,[skin] int

,[status] int

,[publishdate] datetime

,[validata] datetime

,[newtodata] datetime

,[createdate] datetime

,[writedate] datetime

,[inforesource] varchar(50)

,[newskey1] nvarchar(100)

,[newskey2] nvarchar(100)

,[newskey3] nvarchar(100)

,[productkey1] nvarchar(100)

,[productkey2] nvarchar(100)

,[productkey3] nvarchar(100)

,[ordernumber] int

,[newsfrom] varchar(500)

,[viewcount] int

,[item1] varchar(100)

,[item2] varchar(100)

,[item3] varchar(100)

)

SET  @strStartRow = @PageIndex*@PageSize + 1;

SET  @strEndRow   = @PageIndex*@PageSize + @PageSize;

with Categorys(ID,ParentID,CategoryID1,CategoryID2,CategoryID3,CategoryID4,CategoryName,LevelID,CategoryURL,

Status,BGColor,BGImage,TempState,OrderNumber,TitleColor,IsBold)

as

(

select * from CN_New_Category where LevelID=1 and id=@CategoryID

union all

select SC.* FROM CN_New_Category SC

INNER JOIN Categorys C ON SC.ParentID=C.ID

)

insert into @RecordTable

select * from CN_New_Info where categoryid in

(

select id from Categorys

)

SELECT T.*

FROM

(

select row_number() over (order by createdate desc) as SNO,* from @RecordTable

) AS T

WHERE T.SNO>=@strStartRow AND T.SNO<=@strEndRow

SELECT COUNT(ID) AS recordcount FROM @RecordTable

END

GO

测试运行结果截图：