一、资讯类别表ArticleType
资讯表Article
二、递归查询一
with
table1 as
(
select * from ArticleTypewhere Id=1 --定位点成员(也就是初始值或第一个结果集)
union all
select a.* from ArticleType ainner join table1on a.ParentId=table1.Id--递归成员
)
select* from table1
结果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
结果2
四、递归查询结合分页存储过程
alterprocedure UP_Article_ListOfPre_select
@PageIndex int=1,--当前页码
@PageSize int=10,--每页大小
@TypeName nvarchar(20)='彩票资讯',--资讯类别名称
@RecordCount int=0 output--当前条件下的总记录数
as
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
分页结果
五、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;
}