SQL2000父子关联ID递归查询，两个Function，使用游标的技巧

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER     function [dbo].[GetChildByItemId](@ItemId int)    
returns @TempTable table(RequirementId int,LevelID int)
as   
begin   
    DECLARE @requimentID int
    DECLARE ReqCursor CURSOR FOR
    select RequirementId from TM_Requirement where ItemId=@ItemId and OldRequireId=0  --项目ID对应所有根节点
    OPEN ReqCursor 
    FETCH next FROM ReqCursor INTO @requimentID
    WHILE (@@FETCH_STATUS = 0)
         BEGIN
            --print @requimentID
            --select  @requimentID 'ID'
            -- 根据根节点,获取所有的子节点
            Insert into @TempTable 
             select RequirementId,LevelID from GetChildTier(@requimentID) DD
         FETCH NEXT FROM ReqCursor INTO @requimentID
         END
     CLOSE ReqCursor 
     DEALLOCATE ReqCursor 
    return   
end   

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

 

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

ALTER    function [dbo].[GetChildTier](@ID int)    
returns @t table(RequirementId int,LevelID int)    
as   
begin   
    declare @rootId int
    declare @i int   
    set @i = 1    
    select @rootId=[Id] from GetParent(@ID) where fatherId=0  --获取当ID的根节点ID(最顶层的ID)
    insert into @t select @rootId,0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作    
    insert into @t SELECT R.RequirementID,@i FROM TM_Requirement R where R.OldRequireID=@rootId    
   
    while @@rowcount<>0    
    begin   
        set @i = @i + 1    
        insert into @t    
        SELECT R.RequirementID,@i    
        from TM_Requirement R,@t b    
        where   
            R.OldRequireID=b.RequirementId and b.LevelID = @i-1    
    end   
    return   
end   

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/// <summary>
        /// 需求变更统计表
        /// </summary>
        /// <param name="rID">需求编号</param>
        /// <param name="deptID">部门编号</param>
        /// <returns></returns>
        public DataSet GetReportChange(int rID, int departID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select TT.ItemCode,R.RequirementCode,R.RequirementCode,TP.ProblemCode,R.RequirementDescription, ");
            strSql.Append(" TD.DepartmentName,TU.UserName,R.ProposedTime,TS.StateName,TD.DepartmentID ");
            strSql.Append(" from TM_Requirement R, ");
            strSql.Append(" TM_Department TD, ");
            strSql.Append(" (Select StateID,StateName from TM_State where StateTypeID=1) TS, ");
            strSql.Append(" TM_User TU, ");
            strSql.Append( "TM_Item TT, ");
            strSql.Append(" TM_Problem TP, ");

            strSql.Append(" (select * from GetParent(" + rID + ")) F ");
            strSql.Append(" where R.RequirementID= F.ID And OldRequireID!=0 And TT.ItemID=R.ItemID And R.ProblemID=TP.ProblemID ");
            strSql.Append(" And TD.DepartmentID=R.DepartmentID And R.ProposedByID=TU.LoginID And TS.StateID=R.State ");
            if (departID != -1)
            {
                strSql.Append(" And TD.DepartmentID=" + departID);
            }

            strSql.Append("union ");

            strSql.Append(" select TT.ItemCode,R.RequirementCode,R.RequirementCode,TP.ProblemCode,R.RequirementDescription, ");
            strSql.Append(" TD.DepartmentName,TU.UserName,R.ProposedTime,TS.StateName,TD.DepartmentID ");
            strSql.Append(" from TM_Requirement R, ");
            strSql.Append(" TM_Department TD, ");
            strSql.Append(" (Select StateID,StateName from TM_State where StateTypeID=1) TS, ");
            strSql.Append(" TM_User TU, ");
            strSql.Append(" TM_Item TT, ");
            strSql.Append(" TM_Problem TP, ");
            strSql.Append(" (select * from GetChild(" + rID + ")) C ");
            strSql.Append(" where R.RequirementID= C.ID And OldRequireID!=0 And TT.ItemID=R.ItemID And R.ProblemID=TP.ProblemID ");
            strSql.Append(" And TD.DepartmentID=R.DepartmentID And R.ProposedByID=TU.LoginID And TS.StateID=R.State ");
            if (departID != -1)
            {
                strSql.Append(" And TD.DepartmentID=" + departID);
            }

            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            return ds;
        }


        /// <summary>
        /// 需求变更统计表 2012-5-29 by 张小军
        /// </summary>
        /// <param name="rID">需求编号</param>
        /// <param name="ItemId">系统ID</param>
        /// <returns></returns>
        public DataSet GetReportChangeByCondition(int requirementID, int ItemId)
        {
            StringBuilder sb = new StringBuilder();

            if (requirementID == -1 && ItemId !=-1)  //按系统ID来查
            {
                sb.Append("select (AA.LevelID+1) Rank,BB.ItemCode,RequirementCode,ParentCode,ProblemCode,RequirementDescription");
                sb.Append(",DepartmentName,UserName,ProposedTime,StateName,DepartmentID from GetChildByItemId("+ItemId.ToString ()+") AA  ");
                sb.Append(" join (");
                sb.Append(" select R.RequirementID,TT.ItemCode,R.RequirementCode,OldR.RequirementCode ParentCode,TP.ProblemCode,R.RequirementDescription,");
                sb.Append(" TD.DepartmentName,TU.UserName,R.ProposedTime,TS.StateName,TD.DepartmentID from TM_Requirement R ");
                sb.Append(" left join (select RequirementId,RequirementCode from TM_Requirement) OldR on oldR.RequirementId= R.OldRequireID");
                sb.Append(" left join (Select ItemID,ItemCode from TM_Item) TT on TT.ItemID=R.ItemID ");
                sb.Append(" left join (select ProblemID,ProblemCode from TM_Problem) TP on TP.ProblemID=R.ProblemID ");
                sb.Append(" left join (Select DepartmentID,DepartmentName from TM_Department) TD on TD.DepartmentID=R.DepartmentID ");
                sb.Append(" left join (Select LoginID,UserName from TM_User) TU on TU.LoginID=R.ProposedByID ");
                sb.Append(" left join (Select StateID,StateName from TM_State where StateTypeID=1) TS on TS.StateID=R.State ");
                sb.Append(" where R.ItemID="+ItemId.ToString ());
                sb.Append(") BB on AA.RequirementId= BB.RequirementId ");
            }
            else if (requirementID != -1) //只按需求编号来查
            {
                sb.Append("select (AA.LevelID+1) Rank,BB.ItemCode,RequirementCode,ParentCode,ProblemCode,RequirementDescription");
                sb.Append(",DepartmentName,UserName,ProposedTime,StateName,DepartmentID from GetChildTier(" + requirementID + ") AA  ");
                sb.Append(" join (");
                sb.Append(" select R.RequirementID,TT.ItemCode,R.RequirementCode,OldR.RequirementCode ParentCode,TP.ProblemCode,R.RequirementDescription,");
                sb.Append(" TD.DepartmentName,TU.UserName,R.ProposedTime,TS.StateName,TD.DepartmentID from TM_Requirement R ");
                sb.Append(" left join (select RequirementId,RequirementCode from TM_Requirement) OldR on oldR.RequirementId= R.OldRequireID");
                sb.Append(" left join (Select ItemID,ItemCode from TM_Item) TT on TT.ItemID=R.ItemID ");
                sb.Append(" left join (select ProblemID,ProblemCode from TM_Problem) TP on TP.ProblemID=R.ProblemID ");
                sb.Append(" left join (Select DepartmentID,DepartmentName from TM_Department) TD on TD.DepartmentID=R.DepartmentID ");
                sb.Append(" left join (Select LoginID,UserName from TM_User) TU on TU.LoginID=R.ProposedByID ");
                sb.Append(" left join (Select StateID,StateName from TM_State where StateTypeID=1) TS on TS.StateID=R.State ");
                if (ItemId != -1)  //加上系统ID条件
                {
                    sb.Append("where R.ItemID=" + ItemId.ToString());
                }
                sb.Append(") BB on AA.RequirementId= BB.RequirementId");
            }
            else
            {
                return null;
            }
            DataSet ds = DbHelperSQL.Query(sb.ToString());
            return ds;
        }

 

来源：（http://www.szemba.cn/  深圳MBA）