问一些平时遇到的问题

[splory]

1. 在存储过程中使用临时表是不是不好啊，我感觉使用会让存储过程具有更佳的可读性，但是对性能有没有影响就不知道了，最近也被其他人说临时表什么什么不好，最好别用，不知道是不是真的

2. 这个是实际的操作难题，事例如下
CREATE proc seachID(
@UserID int )
as
begin

declare @ID as int
select @ID =ID from MJOperator where UserID=@UserID

declare @re table(ID int,level int)
declare @level int
set @level=0
insert @re select @OID,@level
while @@rowcount>0
begin
set @level=@level+1
insert @re select a.ID ,@level from Operators as a,@re as b where a.Parent =b.ID and b.level=@level-1
end
select a.ID,a.Name from @re as b inner join Operators as a on a.ID = b.ID

end
GO

原来由于表MJOperator中一个UserID对应一个ID，上面的存储过程还能用
但现在我把MJOperator表中的关系改了，一个UserID可能对应多个ID，这种情况下怎么得到UserID对应的每个ID的子节点呢，就是需要查询此UserID下的所有子节点


请高手解决，谢谢

[splory]

上面2问中有点错误
更正：
CREATE proc seachID(
@UserID int )
as
begin

declare @ID as int
select @ID =ID from Operators where UserID=@UserID

declare @re table(ID int,level int)
declare @level int
set @level=0
insert @re select @ID,@level
while @@rowcount>0
begin
set @level=@level+1
insert @re select a.ID ,@level from Operators as a,@re as b where a.Parent =b.ID and b.level=@level-1
end
select a.ID,a.Name from @re as b inner join Operators as a on a.ID = b.ID

end
GO

[wgsasd311]

--try
CREATE proc seachID(
@UserID int )
as
begin
declare @re table(ID int,level int)
declare @level int
set @level=0
insert @re select ID,0 from Operators where UserID=@UserID
while @@rowcount>0
begin
set @level=@level+1
insert @re select a.ID ,@level from Operators as a,@re as b where a.Parent =b.ID and b.level=@level-1
end
select a.ID,a.Name from @re as b inner join Operators as a on a.ID = b.ID

end
GO

[新鲜鱼排]

CREATE proc seachID(
@UserID int )
as
begin

--declare @ID as int
--select @ID =ID from Operators where UserID=@UserID

declare @re table(ID int,level int)
declare @level int
set @level=0
insert @re select ID,@level from Operators where UserID=@UserID
while @@rowcount>0
begin
set @level=@level+1
insert @re select a.ID ,@level from Operators as a,@re as b where a.Parent =b.ID and b.level=@level-1
end
select a.ID,a.Name from @re as b inner join Operators as a on a.ID = b.ID

end
GO

[yoncen]

表變量效率沒有臨時表高..

[splory]

CREATE proc seachID(
@UserID int )
as
begin

declare @ID as int
select @ID =ID from Operators where UserID=@UserID

declare @re table(ID int,level int)
declare @level int
set @level=0
insert @re select @ID,@level
while @@rowcount>0
begin
set @level=@level+1
insert @re select a.ID ,@level from Purviews as a,@re as b where a.Parent =b.ID and b.level=@level-1
end
select a.ID,a.Name from @re as b inner join Operators as a on a.ID = b.ID

end
GO

上面没有讲清楚，是Operators表中的一个UserID对应表中的多个ID，再由ID与PurViews表关联，得到每个ID下的子节点

[coder1]

帮顶

[splory]

第一个问题没人回答呢

[playwarcraft]

我覺得使用#temp臨時表效率不錯啊~~

[liangpei2008]

问题好熟悉！

[playwarcraft]

"上面没有讲清楚，是Operators表中的一个UserID对应表中的多个ID，再由ID与PurViews表关联，得到每个ID下的子节点"
帽似這樣就可以了,還是我理解錯誤?
select * from purViews
where [ID] in (select [ID] from operators where userID=@userID))

[splory]

第一问的答案呢

[areswang]

学习!

[wfliu]

第一个问题，要看你的数据量大小，如果你临时表的数据很大，性能肯定会下降，如果数据量不大，应该没大有影响

[itblog]

:)

[rfq]

学习