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

[haonanernet]

生成编号的存储过程或者函数
编号是树型表的编号
说明
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，而不是a020201

2如果没有值，则先判断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

[haonanernet]

谢谢楼上几位:
问题用根据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
end

GO
----------------------------------------------------------
这样是在一个表中进行操作的,通用性肯定不强
能在存储过程中传人 表名 和 parentid ,然后返回id吗?
看来分数不够给了,我再加上100分,谢谢!

[haonanernet]

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

[子陌红尘]

1、函数中不能使用EXEC语句执行动态SQL，建议改为存储过程。

2、通用性是比较困难的，即便是表名参数化了，列名是否也需要参数化，比如rank等，除非表结构基本类似，呵呵。

[haonanernet]

谢谢:libin_ftsafe
表结构全部一样

我想到一种方法:create function f_getId(@parentid varchar(50),@tb varchar(20))
函数中增加一个参数
然后
if @tb=table1
begin
.....
end

if @tb=table2
begin
.....
end

..............

在程序中调用的时候传两个参数给函数,不过这种表在我的数据库中有10多个,肯定还影响性能
而且看上去这么写有点傻

还是请大哥帮我改写成
存储过程吧,表结构全部一样,谢谢,呵呵

[子陌红尘]

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
GO

exec sp_getId 'tb','a01'
/*
--------
a0104
*/

exec sp_getId 'tb','0'
/*
--------
a03
*/
go


drop procedure sp_getId
drop table tb

[子陌红尘]

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
GO

exec sp_getId 'tb','a01'
/*
a0104
*/

exec sp_getId 'tb','0'
/*
a03
*/
go


drop procedure sp_getId
drop table tb

[haonanernet]

呵呵,多谢,揭贴

[僵哥]

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

[子陌红尘]

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 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
go

select 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

[僵哥]

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

[子陌红尘]

修正一个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')
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
+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
go

select dbo.f_getId('0') as new_id
/*
new_id
------------------------
a03
*/
drop function f_getId
drop table tb

[李雅勇的技术专栏]

测试可以通过，好象还是为了解决分层的问题，如果是，你可以看看这我的文章
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]
GO

OK了，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级部门，而非高一级部门。

[zlp321002]

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

[zlp321002]

--楼主查找以前的贴.