s******n
发帖数: 34 | 1 想要查找一个目录的某个级的子目录, 返回在一个表中.
看看我写的, 挺傻的, 不知道怎么可以优化下
目录关系存在proj_tab里面
create function getSubFolder(@level int, @folderId int)
returns @retab table(folderId int)
as
begin
declare @temp table(folderId int)
insert into @temp select folderId from proj_tab where parentno = @
folderId
select @level = @level -1
while @level <> 0
begin
insert into @retab select folderId from proj_tab where parentno in (
select folderId from @temp)
delete from @temp
insert into @temp | m***0
发帖数: 15 | 2 1) use truncate instead of del
2) depends on depth and size of ur tree, if it can go up to 5 or event more, and you do reads more than writes, you might want to change the logic completely, add one more column called depth, and change column parent to column ancestor, and each node will have all the info about all of its ancestor, if a node in lvl 5 is to be inserted, then it should also insert 4 more rows for each of its ancestor.
in this way, u will have instant results back when u do select.
【在 s******n 的大作中提到】
: 想要查找一个目录的某个级的子目录, 返回在一个表中.
: 看看我写的, 挺傻的, 不知道怎么可以优化下
: 目录关系存在proj_tab里面
: create function getSubFolder(@level int, @folderId int)
: returns @retab table(folderId int)
: as
: begin
: declare @temp table(folderId int)
: insert into @temp select folderId from proj_tab where parentno = @
: folderId
| B*****g
发帖数: 34098 | 3 顶一个。没有connected by，苦呀。
【在 s******n 的大作中提到】
: 想要查找一个目录的某个级的子目录, 返回在一个表中.
: 看看我写的, 挺傻的, 不知道怎么可以优化下
: 目录关系存在proj_tab里面
: create function getSubFolder(@level int, @folderId int)
: returns @retab table(folderId int)
: as
: begin
: declare @temp table(folderId int)
: insert into @temp select folderId from proj_tab where parentno = @
: folderId
| j*****n
发帖数: 1781 | 4 if MS SQL Server 2005, go books online and look for "Recursive Queries Using
Common Table Expressions", there is an example exactly what you want. |
|
