SQLServer中怎么实现层次数据查询

SQLServer中怎么实现层次数据查询，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

　　SQLServer2000层次数据查询的实现方法是什么

　　//层次数据实体

　　publicclassComLevelDataEntity{publicComLevelDataEntity(){searchLevel=0;startWithRoot=true;dataObjectAlias="t";}privateintsearchLevel;//////查询层次///publicintSearchLevel{get{returnsearchLevel;}set{searchLevel=value;}}privatestringdataObjectAlias;//////别名///publicstringDataObjectAlias{get{returndataObjectAlias;}set{dataObjectAlias=value;}}privatestringprotasis;//////条件从句///publicstringProtasis{get{returnstring.IsNullOrEmpty(protasis)string.Empty:string.Concat("and",protasis);}set{protasis=value;}}privateboolstartWithRoot;//////整树搜索///publicboolStartWithRoot{get{returnstartWithRoot;}set{startWithRoot=value;}}privatestringdataObjectName;//////数据对象名称///publicstringDataObjectName{get{returndataObjectName;}set{dataObjectName=value;}}privateintcascadeLevel;//////层级///publicintCascadeLevel{get{returncascadeLevel;}set{cascadeLevel=value;}}privatestringdisplayFieldName;//////显示字段名称///publicstringDisplayFieldName{get{returndisplayFieldName;}set{displayFieldName=value;}}privatestringkeyFieldName;//////键值字段名称///publicstringKeyFieldName{get{returnkeyFieldName;}set{keyFieldName=value;}}privatestringdisplayFieldValue;//////显示字段值///publicstringDisplayFieldValue{get{returndisplayFieldValue;}set{displayFieldValue=value;}}privatestringkeyFieldValue;//////键值字段值///publicstringKeyFieldValue{get{returnkeyFieldValue;}set{keyFieldValue=value;}}privatestringlevelFieldName;//////层次字段名称///publicstringLevelFieldName{get{returnlevelFieldName;}set{levelFieldName=value;}}privatestringlevelFieldValue;//////层次字段值///publicstringLevelFieldValue{get{returnlevelFieldValue;}set{levelFieldValue=value;}}}

　　//sqlserver2000存储过程

　　ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[sp_gettreedata]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[sp_gettreedata]GOSETQUOTED_IDENTIFIERONGOSETANSI_NULLSONGOCREATEPROCEDURE[dbo].[sp_gettreedata](@table_namenvarchar(50),@idnvarchar(50),@namenvarchar(50),@parent_idnvarchar(50),@startIdnvarchar(50),@maxlevelint)ASdeclare@v_idnvarchar(50)declare@v_levelintdeclare@sqlnvarchar(500)declare@v_maxlevelintbegincreatetable#temp(idnvarchar(50),namenvarchar(50),parent_idnvarchar(50))createtable#t1(idnvarchar(50),namenvarchar(50),parent_idnvarchar(50),levelint)deletefromcom_tempset@sql='insertinto#tempselect'+@id+','+@name+','+@parent_id+'from'+@table_nameexecsp_executesql@sqlset@v_level=1set@v_id=@startIdset@v_maxlevel=@maxlevelinsert#t1selecta.id,a.name,a.parent_id,@v_levelfrom#tempawherea.id=@v_idwhile@@rowcount>0beginset@v_level=@v_level+1insert#t1selecta.id,a.name,a.parent_id,@v_levelfrom#tempawherea.parent_idin(selectidfrom#t1wherelevel=@v_level-1)endinsertintoCom_TEMPselecta.level,a.id,a.name,a.parent_id,b.nameparent_namefrom#t1aleftouterjoin#tempbona.parent_id=b.idwherea.level<=@maxlevelorderbya.levelselect*fromcom_temporderbydbo.f_getidpath(id)endgosetquoted_identifieroffgosetansi_nullsongo fromdbo.sysobjectswhereid="object_id(N'[dbo].[f_getidpath]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_getidpath]GOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGOCREATEfunctionf_getidpath(@idchar(36))returnsvarchar(8000)asbegindeclare@revarchar(8000),@pidchar(36)set@re=@idselect@pid=parent_idfromcom_tempwhereid=@idwhile@@rowcount">0select@re=@pid+'.'+@re,@pid=parent_idfromcom_tempwhereid=@pidreturn(@re)endGOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGO

　　//数据访问层方法

　　publicComLevelDataEntity[]PrepareHierarchyData(ComLevelDataEntityentity){System.Data.SqlClient.SqlParameter[]parameters=newSystem.Data.SqlClient.SqlParameter[6];parameters[0]=newSqlParameter();parameters[0].ParameterName="@table_name";parameters[0].Size=50;parameters[0].SqlDbType=System.Data.SqlDbType.VarChar;parameters[1]=newSqlParameter();parameters[1].ParameterName="@id";parameters[1].Size=50;parameters[1].SqlDbType=System.Data.SqlDbType.VarChar;parameters[2]=newSqlParameter();parameters[2].ParameterName="@name";parameters[2].Size=50;parameters[2].SqlDbType=System.Data.SqlDbType.VarChar;parameters[3]=newSqlParameter();parameters[3].ParameterName="@parent_id";parameters[3].Size=50;parameters[3].SqlDbType=System.Data.SqlDbType.VarChar;parameters[4]=newSqlParameter();parameters[4].ParameterName="@startId";parameters[4].Size=50;parameters[4].SqlDbType=System.Data.SqlDbType.VarChar;parameters[5]=newSqlParameter();parameters[5].ParameterName="@maxlevel";parameters[5].SqlDbType=System.Data.SqlDbType.Int;stringspName="sp_gettreedata";if(entity.SearchLevel<=0){parameters[0].Value=entity.DataObjectName;parameters[1].Value=entity.KeyFieldName;parameters[2].Value=entity.DisplayFieldName;parameters[3].Value=entity.LevelFieldName;parameters[4].Value=entity.KeyFieldValue;parameters[5].Value=100;}else{parameters[0].Value=entity.DataObjectName;parameters[1].Value=entity.KeyFieldName;parameters[2].Value=entity.DisplayFieldName;parameters[3].Value=entity.LevelFieldName;parameters[4].Value=entity.KeyFieldValue;parameters[5].Value=entity.SearchLevel;}Listresults=newList();IDbConnectionconnection=IDALProvider.IDAL.PopConnection();IDataReadersqlReader=IDALProvider.IDAL.ExecuteReader(connection,spName,parameters);while(sqlReader.Read()){ComLevelDataEntityresult=newComLevelDataEntity();if(!sqlReader.IsDBNull(0))result.CascadeLevel=(int)sqlReader.GetInt32(0);if(!sqlReader.IsDBNull(1))result.KeyFieldValue=sqlReader.GetString(1);if(!sqlReader.IsDBNull(2))result.DisplayFieldValue=sqlReader.GetString(2);if(!sqlReader.IsDBNull(3))result.LevelFieldValue=sqlReader.GetString(3);result.DataObjectName=entity.DataObjectName;result.DisplayFieldName=entity.DisplayFieldName;result.LevelFieldName=entity.LevelFieldName;result.KeyFieldName=entity.KeyFieldName;results.Add(result);}sqlReader.Close();IDALProvider.IDAL.PushConnection(connection);returnresults.ToArray();} 　　//前端调用代码 　　ComLevelDataEntityentity=newComLevelDataEntity();entity.DataObjectName="COM_DEPART";entity.DataObjectAlias="t";entity.StartWithRoot=true;entity.KeyFieldName="id";entity.LevelFieldName="parent_id";entity.DisplayFieldName="name";entity.KeyFieldValue=SystemLogic.CurrentUser.DATA_ORGANISE_ID;ComLevelDataEntity[]results=IDALProvider.IDAL.PrepareHierarchyData(entity);this.tvTree.DataSource=results;this.tvTree.DataBind();  

关于SQLServer中怎么实现层次数据查询问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。