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

99次阅读

共计 6242 个字符，预计需要花费 16 分钟才能阅读完成。

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 [email protected]*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 中怎么实现层次数据查询问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。

正文完