SQL2005递归查询+存储过程分页

超、凢脫俗 2022-06-07 23:38 225阅读 0赞

一、资讯类别表ArticleType

![1343287504_9563.jpg][]

资讯表Article

![1343287667_8113.jpg][]

二、递归查询一

with

table1  as

(

select \* from ArticleTypewhere Id=1 \--定位点成员（也就是初始值或第一个结果集）

union all

select a.\* from ArticleType ainner join table1on a.ParentId=table1.Id\--递归成员

)

select\* from table1

![1343287791_1739.jpg][]结果1  
三、递归查询二

with

table1  as

(

select \* from ArticleTypewhere Id=4 \--定位点成员（也就是初始值或第一个结果集）

union all

select a.\* from ArticleType ainner join table1on a.Id=table1.ParentId\--递归成员

)

select\* from table1

![1343287848_9257.jpg][]结果2

四、递归查询结合分页存储过程

alterprocedure UP\_Article\_ListOfPre\_select

@PageIndex  int=1,\--当前页码

@PageSize  int=10,\--每页大小

@TypeName  nvarchar(20)='彩票资讯',\--资讯类别名称

@RecordCount  int=0 output\--当前条件下的总记录数

begin

declare @StartIndexint,@EndIndex int

declare @Idint

set @StartIndex=(@PageIndex\-1)\*@PageSize

set @EndIndex=@StartIndex\+@PageSize\-1

select @Id=Idfrom ArticleType where TypeName=@TypeName

;with

table1  as

(

select Id from ArticleType  where Id=@Id \--定位点成员（也就是初始值或第一个结果集）

union all

select a.Id from ArticleType ainner join table1on a.ParentId=table1.Id\--递归成员

)

\-- select \* from table1 --CTE with之后第一句必须使用CTE的select。CTE的生命周期只是在第一次使用之后就消亡。

select Idinto \#mytb from table1

\--获取当前条件下的总记录数

select @RecordCount=count(Id)from Article where TypeIdin(select Id from \#mytb)

\--分页查询

select\* from

(

select row\_number()  over(orderby AddTime desc)as Row,Id, Title,AddTimefrom Article

where TypeId in(select Idfrom \#mytb)

) awhere Row between @StartIndexand @EndIndex

end

![1343288045_1625.jpg][]分页结果

五、C\#调用代码

///<summary>

///根据条件分页查询信息

///</summary>

///<param name="pageIndex">当前页码</param>

///<param name="pageSize">每页大小</param>

///<param name="typeName">资讯类别名称</param>

///<param name="recordCount">out参数，当前条件下的总记录数</param>

///<returns>分页查询后的列表</returns>

public  DataTable GetList(int pageIndex,  int pageSize, string typeName,  out int recordCount)

SqlParameter\[\] parms = \{

new SqlParameter("@PageIndex",SqlDbType.Int),

new SqlParameter("@PageSize",SqlDbType.Int),

new SqlParameter("@TypeName",SqlDbType.NVarChar,20),

new SqlParameter("@RecordCount",SqlDbType.Int)

\};

parms\[0\].Value = pageIndex;

parms\[1\].Value = pageSize;

parms\[2\].Value = typeName;

parms\[3\].Direction = ParameterDirection.Output;

DataTable dt =  SQLHelper.GetDataTable(CommandType.StoredProcedure, "UP\_Article\_ListOfPre\_select", parms);

recordCount = Convert.ToInt32(parms\[3\].Value);

return dt;

[1343287504_9563.jpg]: https://img-my.csdn.net/uploads/201207/26/1343287504_9563.jpg
[1343287667_8113.jpg]: https://img-my.csdn.net/uploads/201207/26/1343287667_8113.jpg
[1343287791_1739.jpg]: https://img-my.csdn.net/uploads/201207/26/1343287791_1739.jpg
[1343287848_9257.jpg]: https://img-my.csdn.net/uploads/201207/26/1343287848_9257.jpg
[1343288045_1625.jpg]: https://img-my.csdn.net/uploads/201207/26/1343288045_1625.jpg