SQL中的树型编号处理
1在朋友的程序里面看见这段代码,用于treeview生成新编号的
编码
闲的实在无聊就用存储过程写了下,感觉调用起来也挺好:)
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
![](/Images/OutliningIndicators/None.gif)
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
没头没尾,可能只有朋友看见了才懂我在说什么
![](/Images/OutliningIndicators/ContractedBlock.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)