SQL中的树型编号处理

1在朋友的程序里面看见这段代码，用于treeview生成新编号的

编码 #region 编码
        /**//// <param name="table">数据表</param>
        /// <param name="parentid">父id</param>
        /// 通过以上参数求出下级目录的id
        public string Id(string table,string parentid)
        {
            DataSet dbset=new DataSet();
            string sql="select * from "+table+" where parentid='"+parentid+"'";
            dbset=GetDs(sql);
            string s="";
            if (dbset.Tables[0].Rows.Count>0)
            {
                //已经存在子集
                string strid=dbset.Tables[0].Rows[0]["id"].ToString();
                int max=0;
                max=int.Parse(strid.Substring(1,strid.Length-1));
                for (int i=0;i<dbset.Tables[0].Rows.Count;i++)
                {
                    strid=dbset.Tables[0].Rows[i]["id"].ToString();
                    if (int.Parse(strid.ToString().Substring(1,strid.Length-1))>=max)
                    {
                        max=int.Parse(strid.Substring(1,strid.Length-1));
                    }
                }
                max+=1;
                if (max.ToString().Length<strid.Length-1)
                {
                    for (int i=0;i<strid.Length-1-max.ToString().Length;i++)
                    {
                        s=s+"0";
                    }
                }
                s="a"+s+max.ToString();
            }
            else
            {
                //还没有子集
                if (parentid=="0")
                {
                    //根目录
                    s="a01";
                }
                else
                {
                    //子目录
                    sql="select * from "+table+" where id='"+parentid+"'";
                    dbset=GetDs(sql);
                    if (dbset.Tables[0].Rows.Count>0)
                    {
                        for (int i=0;i<int.Parse(dbset.Tables[0].Rows[0]["rank"].ToString());i++)
                        {
                            s=s+"0";
                        }
                        s=parentid+s+"1";
                    }
                }
            }
            return s;

        }

闲的实在无聊就用存储过程写了下，感觉调用起来也挺好：）

create   procedure  sp_getId( @tname   nvarchar ( 100 ), @parentid   varchar ( 50 ))
as
begin
     declare   @sql   nvarchar ( 4000 )
     set   @sql = N ' select 
                 case @parentid when  '' 0 ''  then  '' a ''  else @parentid end +
                     right(cast(POWER(10,isnull((select rank from  ' + @tname + N '  where id=@parentid),1)) as varchar(50))+rtrim(isnull(max(stuff(id,1,len(parentid), '''' )),0)+1),
                     case 
                       when isnull((select rank from  ' + @tname + N '  where id=@parentid),1)<2 then 2 
                       else 
                          case 
                            when not exists(select 1 from  ' + @tname + N '  where parentid=@parentid)
                              then (select rank from  ' + @tname + N '  where id=@parentid)+1 
                            else
                              (select rank from  ' + @tname + N '  where parentid=@parentid)
                          end
                     end) 
    from  ' + @tname + N '  where parentid=@parentid '
     exec  sp_executesql  @sql , N ' @parentid varchar(50) ' , @parentid
end
GO

函数这么写:

create   function  f_getId( @parentid   varchar ( 50 ))
returns   varchar ( 50 )
as
begin
     declare   @id   varchar ( 50 )
     select  
         @id =   case   @parentid   when   ' 0 '   then   ' a '   else   @parentid   end   -- a01(表中无数据)/a06表中数据，是第一级
             + right ( cast ( POWER ( 10 , isnull (( select  rank  from  notbm  where  id = @parentid ), 1 ))  as   varchar ( 50 )) + rtrim ( isnull ( max ( stuff (id, 1 , len (parentid), '' )), 0 ) + 1 ),
          case   when  
isnull (( select  rank  from  notbm  where  id = @parentid ), 1 ) < 2   then   2  
else  
case   when   not   exists ( select   1   from  notbm  where  parentid = @parentid )
then   
( select  rank  from  notbm  where  id = @parentid ) + 1   else
( select  rank  from  notbm  where  parentid = @parentid )
  end   end ) 
     from  notbm 
     where  parentid = @parentid
     return   @id
end

GO

和程序差不多的SQL写法:

declare   @Result   as   varchar ( 50 )
declare   @tmpChar   as   varchar ( 50 )
declare   @iLen   as   Integer
if   Exists ( select   1   from   [ table ]   where  parentid = ' [parentid] ' )
-- 找得到parentid='parentid'的值
   begin
     select   @Result = max (id), @iLen = rank  from   [ table ]   where  parentid = ' [parentid] '   group   by  rank
-- 相同parentid,其rank相同
     select   @tmpChar = left ( @Result , len ( @result ) - @iLen )
     select   @Result = ' 1 ' + right ( @Result , @iLen )
     select   @Result = @tmpChar + right ( cast ( cast ( @Result   as   bigint ) + 1   as   varchar ( 50 )), @iLen )
   end
else
   begin
     if   [ parentid ] = ' 0 '
       begin
         select   @Result = ' a01 '
       end
     else
       begin
         select   @Result = id + replace ( space (rank), '   ' , ' 0 ' ) + ' 1 '   from   [ table ]   where  id = ' [parentid] '
       end
   end

没头没尾，可能只有朋友看见了才懂我在说什么

转载于:https://www.cnblogs.com/kasafuma/archive/2006/03/30/362777.html