特殊分类查询(求存储过程)

[surferc]

类别分三级
tb1
id sname nlevel
1 A -1
2 B -1
3 A1 1
4 B1 1
5 A1-1 2

tb2
childid Fid
3 1
3 2
4 2
5 3


调用存储过程exec spclass(rootid,childid) root 根ID | Childid 子ID
示例1：
exec spclass(1,5)
得到结果
id sname nlevel
1 A -1
3 A1 1
5 A1-1 2

示例2：
exec spclass(2,5)
得到结果
2 B -1
3 A1 1
5 A1-1 2

示例3：
exec spclass(2,5)
得到结果
id sname nlevel
2 B -1
5 A1-1 2

就是有可能2级类别同时属于不同的rootID
哪位给个存储过程谢谢啦~~~~

[surferc]

补充：或者有其它的设计思路(表结构/存储过程)

[surferc]

楼上的不行吧，不过还是谢谢你了。

[Softlee81307]

--------------------下面是建表結構-----------------------
Create Table tb1(
id1 int,
sname varchar(10),
nlevel int
)
create table tb2(
childid int,
fid int
)
--------------------------
Create Table tb3(vk int null)


insert into tb1 values( 1, 'A', -1)
insert into tb1 values(2 , 'B', -1)
insert into tb1 values(3 , 'A1', 1)
insert into tb1 values(4, 'B1', 1)
insert into tb1 values(5 , 'A1-1', 2)

insert into tb2 values( 3, 1)
insert into tb2 values(3 , 2)
insert into tb2 values(4 , 2)
insert into tb2 values(5 , 3)

select * from tb1

select * from tb2
-----------------------------------下面是存過程 --------------------------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p]
go
Create Procedure tb1_p
@i int,
@p int
as
Declare @k int
Declare @@s int
if not exists(select * from tb3 where vk>=@p)
select * from tb1 where id1=@i
Declare kk cursor local for
select childid from tb2 where fid=@i and childid<=@p
open kk
fetch next from kk into @k
while @@fetch_status=0
begin
exec tb1_p @k,@p
if not exists(select * from tb3 where vk>=@p)
update tb3 set vk=@k
fetch next from kk into @k
end
close kk
deallocate kk
----------------------------------------下面是第二個過程 ,調用第一個過程----------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p1]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p1]
go
Create Procedure tb1_p1
@i int,
@p int
as
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p
-------------------------------------------------結束-------------------------------------------------------------------
-- tb1_p1 2,5 "這是執行語句 查結果

[Softlee81307]

先執行這下面的
--------------------下面是建表結構-----------------------
Create Table tb1(
id1 int,
sname varchar(10),
nlevel int
)
create table tb2(
childid int,
fid int
)
--------------------------
Create Table tb3(vk int null)


insert into tb1 values( 1, 'A', -1)
insert into tb1 values(2 , 'B', -1)
insert into tb1 values(3 , 'A1', 1)
insert into tb1 values(4, 'B1', 1)
insert into tb1 values(5 , 'A1-1', 2)

insert into tb2 values( 3, 1)
insert into tb2 values(3 , 2)
insert into tb2 values(4 , 2)
insert into tb2 values(5 , 3)

select * from tb1

select * from tb2

[Softlee81307]

再執行第一個過程
-----------------------------------下面是存過程 --------------------------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p]
go
Create Procedure tb1_p
@i int,
@p int
as
Declare @k int
Declare @@s int
if not exists(select * from tb3 where vk>=@p)
select * from tb1 where id1=@i
Declare kk cursor local for
select childid from tb2 where fid=@i and childid<=@p
open kk
fetch next from kk into @k
while @@fetch_status=0
begin
exec tb1_p @k,@p
if not exists(select * from tb3 where vk>=@p)
update tb3 set vk=@k
fetch next from kk into @k
end
close kk
deallocate kk

[Ariese]

----------------------------------------下面是第二個過程 ,調用第一個過程----------------
If Exists(select * from sysobjects where id=object_id(N'[dbo].[tb1_p1]') and objectproperty(id,N'isProcedure')=1 )
Drop Procedure [DBO].[tb1_p1]
go
Create Procedure tb1_p1
@i int,
@p int
as
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p

[Ariese]

tb1_p1 2,5 "這是執行語句 查結果

[surferc]

谢谢谢谢 Softlee81307(孔腎) 我先看看有问题再提，保证结贴。

[尚和空四]

create proc zcf_f1
@prodId int
as
select prodName,prodId,bomNum
from prod
where prodId = @prodId

[surferc]

To: Ariese(果冻)

tb1_p1 里面的这段
Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p

因为是多人同时访问（访问量大）会不会出现问题？

[surferc]

另：我上面的示例3错了应该是
示例3：
exec spclass(2,3)
得到结果
id sname nlevel
2 B -1
3 A1 1

[Softlee81307]

tb1_p1 2,5
應用 tb1_p1 2,3
會是你要的結果 ,開如點是2，子級是3時就會斷開

[zjcxc]

--只有三级? 写这样一个自定义就行了

create function f_spclass(
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a
where a.id=@rootid
or a.id=@childid
or a.id=(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid))
go


--调用
select * from f_spclass(1,5)

select * from f_spclass(2,5)

select * from f_spclass(2,3)

[zjcxc]

--用自定义函数

--示例
--测试数据
create table tb1(id int,sname nvarchar(10),nlevel int)
insert tb1 select 1,'A' ,-1
union all select 2,'B' ,-1
union all select 3,'A1' ,1
union all select 4,'B1' ,1
union all select 5,'A1-1',2

create table tb2(childid int,Fid int)
insert tb2 select 3,1
union all select 3,2
union all select 4,2
union all select 5,3
go

--自定义函数
create function f_spclass(
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a
where a.id=@rootid
or a.id=@childid
or a.id=(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid))
go

--调用
select * from f_spclass(1,5)

select * from f_spclass(2,5)

select * from f_spclass(2,3)
go

--删除测试
drop table tb1,tb2
drop function f_spclass

/*--测试结果

id sname nlevel
----------- ---------- -----------
1 A -1
3 A1 1
5 A1-1 2

（所影响的行数为 3 行）

id sname nlevel
----------- ---------- -----------
2 B -1
3 A1 1
5 A1-1 2

（所影响的行数为 3 行）

id sname nlevel
----------- ---------- -----------
2 B -1
3 A1 1

（所影响的行数为 2 行）
--*/

[zjcxc]

--自定义函数也可以改成这样

create function f_spclass(
@rootid int,
@childid int
)returns table
as
return(
select a.*
from tb1 a,(
select a.childid
from tb2 a,tb2 b
where a.Fid=@rootid
and b.childid=@childid
and a.childid=b.Fid
union select @rootid
union select @childid
)b where a.id=b.childid)

[surferc]

晕倒.........拜邹老师....初学新手没用过自定义函数. -0_-bbb

这贴子终于可以结了。

PS:我是用存储过程select得到三级并插入临时表得到的。

[surferc]

奇怪啊！我结贴了可是发现Softlee81307(孔腎) 和邹老师都没得分这是怎么回事？

[surferc]

是这样的我想知道由于对tb3频繁的读写(访问量大)会不会有问题。因为这里的tb3可以看成一个全局的表吧？

Delete from tb3
insert into tb3 values(0)
exec tb1_p @i,@p

因为是多人同时访问（访问量大）会不会出现问题？