生成编号的存储过程或者函数(这个分数不好拿啊,难)

Question

生成编号的存储过程或者函数
编号是树型表的编号
说明
1表 tb
字段
id          varchar(50)  
name        varchar(50)          
code        varchar(50) 
rank        int
parentid    varchar(50) 
数据：id     name    code   rank   parentid
a01     ex      A       1      0
a0101   ex      01      2      a01
a0102   ex      02      2      a01
a0103   ex      03      2      a01
a02     ex      B       1       0
a0201   ex      01      2      a02
a0202   ex      02      2      a02
a0202001  ex    01      3      a0202
a0202002  ex    02      3      a0202如果给出一个参数parentid求出id
我现在用c#求的
方法是：
先 根据sql="select * from "+table+" where parentid='"+parentid+"'";
1如果有值，则求出值中最大的id再加上1返回id就是所求
 注意：这里有一种情况就是 当parentid=a0202的时候，为什么下级是a0202001，而不是a0202012如果没有值，则先判断parentid是否等于"0"
如果等于返回id="a01"
如果不等于"0"则根据sql="select * from "+table+" where id='"+parentid+"'";求出rank---> id=parentid+rank个"0"+"1"例如：parentid=a0202002
则他会判断没有值，走上面的2，然后判断不等于"0",接着又sql="select * from "+table+" where id='a0202002'"
求出rank等于3，则通过id=parentid+rank个"0"+"1"则应该返回id=a02020020001再例如：parentid=a01则它会通过select * from "+table+" where parentid='"+parentid+"'判断有值，则求出最大的+1应该返回a0104再例如：parentid=0 则应该返回a03

Answer 1

怎麼想就怎麼做,關鍵是付諸於實踐
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

Answer 2

create table tb(
id          varchar(50),
name        varchar(50),
code        varchar(50),
rank        int,
parentid    varchar(50))
insert into tb select rtrim('a01     '),'ex',rtrim('A '),1,rtrim('0    ')
insert into tb select rtrim('a0101   '),'ex',rtrim('01'),2,rtrim('a01  ')
insert into tb select rtrim('a0102   '),'ex',rtrim('02'),2,rtrim('a01  ')
insert into tb select rtrim('a0103   '),'ex',rtrim('03'),2,rtrim('a01  ')
insert into tb select rtrim('a02     '),'ex',rtrim('B '),1,rtrim('0    ')
insert into tb select rtrim('a0201   '),'ex',rtrim('01'),2,rtrim('a02  ')
insert into tb select rtrim('a0202   '),'ex',rtrim('02'),2,rtrim('a02  ')
insert into tb select rtrim('a0202001'),'ex',rtrim('01'),3,rtrim('a0202')
insert into tb select rtrim('a0202002'),'ex',rtrim('02'),3,rtrim('a0202')
gocreate 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
            +right('000'+rtrim(isnull(max(stuff(id,1,len(parentid),'')),0)+1),case when (select rank from tb where id=@parentid)<2 then 2 else 3 end) 
    from tb 
    where parentid=@parentid
    return @id
end
goselect dbo.f_getId(id) as new_id from tb/*
new_id
------------------------ 
a0104
a0101001
a0102001
a0103001
a0203
a0201001
a0202003
a0202001001
a0202002001
*/drop function f_getId
drop table tb

Answer 3

不過這當中有一個問題,樓主所使用的表名是動態的,並且需要返回值,這一點得請高人出馬看有什麼辦法好解決的.否則只有是通過類似於上面的代碼執行批處理,然後在最後添加一條
select @Result
來得到結果,當然上面的代碼當中parentid沒有使用變量,改一下就好.

Answer 4

修正一个bug：
------------------------------------------------------------------------------------------------------------------------------
create table tb(
id          varchar(50),
name        varchar(50),
code        varchar(50),
rank        int,
parentid    varchar(50))
insert into tb select rtrim('a01     '),'ex',rtrim('A '),1,rtrim('0    ')
insert into tb select rtrim('a0101   '),'ex',rtrim('01'),2,rtrim('a01  ')
insert into tb select rtrim('a0102   '),'ex',rtrim('02'),2,rtrim('a01  ')
insert into tb select rtrim('a0103   '),'ex',rtrim('03'),2,rtrim('a01  ')
insert into tb select rtrim('a02     '),'ex',rtrim('B '),1,rtrim('0    ')
insert into tb select rtrim('a0201   '),'ex',rtrim('01'),2,rtrim('a02  ')
insert into tb select rtrim('a0202   '),'ex',rtrim('02'),2,rtrim('a02  ')
insert into tb select rtrim('a0202001'),'ex',rtrim('01'),3,rtrim('a0202')
insert into tb select rtrim('a0202002'),'ex',rtrim('02'),3,rtrim('a0202')
gocreate 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
            +right('000'+rtrim(isnull(max(stuff(id,1,len(parentid),'')),0)+1),case when isnull((select rank from tb where id=@parentid),1)<2 then 2 else 3 end) 
    from tb 
    where parentid=@parentid
    return @id
end
goselect dbo.f_getId('0') as new_id
/*
new_id
------------------------ 
a03
*/
drop function f_getId
drop table tb

Answer 5

测试可以通过，好象还是为了解决分层的问题，如果是，你可以看看这我的文章
http://blog.csdn.net/geniusli/archive/2006/03/16/626155.aspx 关于数据库设计中的分级分层问题的总结（适用于组织结构图及家谱等问题） 
大家在很多地方都会遇到诸如此类的问题，如：
在一个组织中，从高层领导到中层领导到普通职工，有着一种分级的关系，这些关系在关系型数据库中表示时，有它特别的方法。
有些人把它们分别建立在不同的表中，建立相应的关系，这虽然是一种解决办法，但如果在应用中，它会给软件带来诸多不便，特别是程序健壮性方法，如果此时需要多加一个职位，这种解决办法就需要多加一张表，那此时对应用程序的改动将可想而知。
我在此处提供的方法并不是什么独特的方法，而是大家所属知的方法，但在我们的论坛上经常有人问到并且我再三进行解答。
因此我一方面将这些帖的问题收集一下，再加上全面的解释，组织成这样一篇文章，希望对大家有所帮助。
如有不正确之处请大家提出意见。问题一：
想设计一个组织结构管理的数据库模型，能让用户自由定义组织结构层次和关系，大家有没有好的意见和经典的结构模型？解决：
其实一个层次的模型在关系数据库本身就是个问题。根本问题在于关系模型的第一范式就要求属性分为不可再分，这样的话就直接造成层次结构实现的复杂性。如果一个属性可以包含多个内容的话，那就可以有这样一个属性，叫做"下级"的属性，它包含了此成员的所有下级，但这样是不可取的。
虽然这样，但组织结构这个问题比较容易解决。原因是组织结构本身的结构比较简单。
在设计的时候只要做一个指向本身主键的属性，也就是说这个属性的外键就是本表的主键，在查询的时候很简单，只要做一个自连接就可以了，也就是
select b.* from (t1) as a,(t1) as b where a.p1=b.p2 and a.p1="bill"
t1为表，p1为主键，p2为下属，它指向它的上级p1。上面这条语句查询的就是bill的下属名单。
select a.* from (t1) as a,(t1) as b where a.p1=b.p2 and b.p2="bill"
这条语句查询的就是bill的上级，这就实现了多层。问题二：
如何在数据库中表示这样的数据，希望在论坛中，做出把一些人加入黑名单的功能，描述：
这个数据库保存每个用户所讨厌的用户,例如:
mary tom john
sam joe
以上表示mary讨厌tom和john, sam讨厌joe解决：
这在关系代数里是一个自连接多对多关系数据库，ER图不便画出，在这里我只说明解决方案。
当然有一个注册用户表，如：
CREATE TABLE [TB_user] (
[userid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[username] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_TB_user] PRIMARY KEY  CLUSTERED 
(
[userid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
再来就是保存讨厌对象的表：
CREATE TABLE [bedfriend] (
[bedid] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[userid1] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[userid2] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_bedfriend] PRIMARY KEY  CLUSTERED 
(
[bedid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GOOK了，TB_user里存的是注册用户
bedfrend表里存的是如下信息：
bedfrend
---------------------
bedid    userid1    userid2
1          1           2
2          1           5
3          1           8
4          2           3
5          2           4
6          3           6注意有一点，就是，这个关系设计的是如果bill讨厌kate，那kate不一定讨厌bill
原因大家都知道，kate喜欢bill嘛，bill喜欢nana
问题三：
用户表：uid[用户ID],depid[所属部门ID]
部门表：depid[部门ID],pdepid[上级部门ID]函数：UidInDepSub(depid int,uid int) --判断uid是否属于depid[部门ID]或其下级部门ID。
结果：select * from news where dbo.UidInDepSub(depid,uid)>1 --可以用于select中判断，此处需要多层递归判断，层次不定。请问此函数应该怎么写？解决：
CREATE TABLE [depart] (
 [depid] [int] NOT NULL ,
 [pdepid] [int] NULL ,
 CONSTRAINT [PK_depart] PRIMARY KEY  CLUSTERED 
 (
  [depid]
 )  ON [PRIMARY] ,
 CONSTRAINT [FK_depart_depart] FOREIGN KEY 
 (
  [pdepid]
 ) REFERENCES [depart] (
  [depid]
 )
) ON [PRIMARY]--向depart加入数据
insert into depart values(1,null)--顶层的上级一定要为null
insert into depart values(2,1)
insert into depart values(3,1)
insert into depart values(4,2)
insert into depart values(5,2)
insert into depart values(6,3)
insert into depart values(7,4)
insert into depart values(8,4)
insert into depart values(9,5)
insert into depart values(10,6)
create function billfun(@departid int,@uid int)
returns int
as
begin
 declare @temp int
 SELECT @temp=pdepid
 FROM dbo.depart
 WHERE (depid = @uid)
 if(@temp=@departid)
  RETURN 1
 if(@temp is null)
  RETURN 0
 RETURN (dbo.billfun(@departid,@temp))
end
select dbo.billfun(2,8)
--为1
select dbo.billfun(3,8)
--为0上面的函数是一个递归函数，楼主当时看我的程序短还以为不能用呢。其实它就是一直递归查询uid是不是属于它们的高n级部门，而非高一级部门。

Answer 6

--递归在SQL 2000 和SQL 2005中的方法.都有了!

Answer 7

谢谢楼上几位:
    问题用根据libin_ftsafe的函数解决了,但是大哥的最多支持3级,我改了一下:
  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 
            +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
endGO
----------------------------------------------------------
这样是在一个表中进行操作的,通用性肯定不强
能在存储过程中传人 表名 和 parentid ,然后返回id吗?
看来分数不够给了,我再加上100分,谢谢!

Answer 8

呵呵,加分成功,变成200分了
不是我懒,我实在对存储过程不熟悉,以前都在程序中直接写sql,
看来三层架构还是有好处的

Answer 9

1、函数中不能使用EXEC语句执行动态SQL，建议改为存储过程。2、通用性是比较困难的，即便是表名参数化了，列名是否也需要参数化，比如rank等，除非表结构基本类似，呵呵。

Answer 10

谢谢:libin_ftsafe
表结构全部一样我想到一种方法:create function f_getId(@parentid varchar(50),@tb varchar(20))
函数中增加一个参数
然后
if @tb=table1
begin
.....
endif @tb=table2
begin
.....
end..............在程序中调用的时候传两个参数给函数,不过这种表在我的数据库中有10多个,肯定还影响性能
而且看上去这么写有点傻还是请大哥帮我改写成
存储过程吧,表结构全部一样,谢谢,呵呵

Answer 11

create table tb(
id          varchar(50),
name        varchar(50),
code        varchar(50),
rank        int,
parentid    varchar(50))
insert into tb select rtrim('a01     '),'ex',rtrim('A '),1,rtrim('0    ')
insert into tb select rtrim('a0101   '),'ex',rtrim('01'),2,rtrim('a01  ')
insert into tb select rtrim('a0102   '),'ex',rtrim('02'),2,rtrim('a01  ')
insert into tb select rtrim('a0103   '),'ex',rtrim('03'),2,rtrim('a01  ')
insert into tb select rtrim('a02     '),'ex',rtrim('B '),1,rtrim('0    ')
insert into tb select rtrim('a0201   '),'ex',rtrim('01'),2,rtrim('a02  ')
insert into tb select rtrim('a0202   '),'ex',rtrim('02'),2,rtrim('a02  ')
insert into tb select rtrim('a0202001'),'ex',rtrim('01'),3,rtrim('a0202')
insert into tb select rtrim('a0202002'),'ex',rtrim('02'),3,rtrim('a0202')
go
create procedure sp_getId(@tname nvarchar(100),@parentid varchar(50))
as
begin
    declare @id varchar(50),@sql nvarchar(4000)
    set @sql=N'select 
         @id= 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'@id varchar(50) output,@parentid varchar(50)',
                       @id output,@parentid
    select @id
end
GOexec sp_getId 'tb','a01'
/*
--------
a0104
*/exec sp_getId 'tb','0'
/*
--------
a03
*/
go
drop procedure sp_getId
drop table tb

Answer 12

create table tb(
id          varchar(50),
name        varchar(50),
code        varchar(50),
rank        int,
parentid    varchar(50))
insert into tb select rtrim('a01     '),'ex',rtrim('A '),1,rtrim('0    ')
insert into tb select rtrim('a0101   '),'ex',rtrim('01'),2,rtrim('a01  ')
insert into tb select rtrim('a0102   '),'ex',rtrim('02'),2,rtrim('a01  ')
insert into tb select rtrim('a0103   '),'ex',rtrim('03'),2,rtrim('a01  ')
insert into tb select rtrim('a02     '),'ex',rtrim('B '),1,rtrim('0    ')
insert into tb select rtrim('a0201   '),'ex',rtrim('01'),2,rtrim('a02  ')
insert into tb select rtrim('a0202   '),'ex',rtrim('02'),2,rtrim('a02  ')
insert into tb select rtrim('a0202001'),'ex',rtrim('01'),3,rtrim('a0202')
insert into tb select rtrim('a0202002'),'ex',rtrim('02'),3,rtrim('a0202')
go
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
GOexec sp_getId 'tb','a01'
/*
a0104
*/exec sp_getId 'tb','0'
/*
a03
*/
go
drop procedure sp_getId
drop table tb