指定深度的层级查询。。。

Question

简略数据如下：<pre>
用户名 上级
admin #
me #
you admin
he you
pit he
she he
ting she
oh ting
yumen admin
ai yumen
fun ai
log admin
ling log
pool ling
</pre>只给了比较直接简单的数据关系，现在想做如下两种查询：
1.给出用户名和层级深度，求它的上级，例如：
给出oh和3，它的查询结果应该是：ting,she,he
给出oh和5，它的查询结果应该是：ting,she,he,you,admin,#
给出oh和6，它的查询结果应该是：ting,she,he,you,admin,#,#（没有了以#表示）
2.给出用户名和层级深度，求此用户此深度的下级，例如：
给出admin和1，它的查询结果应该是：you,yumen,ai,log
给出admin和3，它的查询结果应该是：pit,she,fun,pool环境是.net2.0+mssql2000数据实在是外行，希望各位高手能给予帮助

Answer 1

--> --> (Roy)生成測試數據
 
if not object_id('T') is null
drop table T
Go
Create table T([用户名] nvarchar(5),[上级] nvarchar(5))
Insert T
select N'admin',N'#' union all
select N'me',N'#' union all
select N'you',N'admin' union all
select N'he',N'you' union all
select N'pit',N'he' union all
select N'she',N'he' union all
select N'ting',N'she' union all
select N'oh',N'ting' union all
select N'yumen',N'admin' union all
select N'ai',N'yumen' union all
select N'fun',N'ai' union all
select N'log',N'admin' union all
select N'ling',N'log' union all
select N'pool',N'ling'
Goif object_id('F_BOM','FN') is not null
    drop function F_BOM
go
create function F_BOM(@UserName nvarchar(50),@Lev int=1)
returns nvarchar(1000)
as
begin
    declare @s nvarchar(1000),@Name nvarchar(50),@i int
    set @i=0
    lab:
    set @i=@i+1
    set @Name =(select [上级] from T where [用户名]=@UserName)
    select @UserName=[上级] from T where [用户名]=@UserName
    if @i<=@Lev
    begin
    if @Name is not null
        set @s=isnull(@s+',','')+@Name
    else 
set @s=@s+',#'
     goto lab
     end
    return @s
end
go
select dbo.F_BOM('oh',3)
/*
ting,she,he
*/
select dbo.F_BOM('oh',7)
/*
ting,she,he,you,admin,#,#
*/

Answer 2

本帖最后由 roy_88 于 2011-11-23 09:40:01 编辑

  

Answer 3

用户名目前暂时来说是唯一的，本来用用.net函数递归可以查询的到，但是如果数据量大了，效率就比较低了
@roy_88，谢谢，我先测试一下

Answer 4

@roy_88
经过测试，得出的结果确实正确，能不能再改进一些呢？
上面那两个函数返回的是一列，现在想要返回多列，就是说上面是返回she,he,me这样的
现在要返回三行
she
he
me
这样
还有，提个疑问：
好像下面那个查找下级的函数，是声明了一个表@t，然后往里面插入查询到的数据是吧，这样多用户在同时查询数据的时候不会互相影响吧？
因为没怎么学习过数据库，希望@roy_88能再次给予帮助谢谢

Answer 5

建议你先把SQL2000升级到SQL2008，这样可以用CONNECT BY

Answer 6

]
给出oh和6，它的查询结果应该是：ting,she,he,you,admin,#,#（没有了以#表示）
--------------------當不足層數時記錄全用"#"符號？

Answer 7

--> --> (Roy)生成測試數據
 
if not object_id('T') is null
    drop table T
Go
Create table T([用户名] nvarchar(5),[上级] nvarchar(5))
Insert T
select N'admin',N'#' union all
select N'me',N'#' union all
select N'you',N'admin' union all
select N'he',N'you' union all
select N'pit',N'he' union all
select N'she',N'he' union all
select N'ting',N'she' union all
select N'oh',N'ting' union all
select N'yumen',N'admin' union all
select N'ai',N'yumen' union all
select N'fun',N'ai' union all
select N'log',N'admin' union all
select N'ling',N'log' union all
select N'pool',N'ling'
Go
--查上級 
if object_id('F_BOM','FN') is not null
    drop function F_BOM
go
create function F_BOM(@UserName nvarchar(50),@Lev int=1)
returns @T TABLE([用户名] nvarchar(5),Lev int )
as
begin
    declare @Name nvarchar(50),@i int
    set @i=0
    lab:
    set @i=@i+1
    set @Name =(select [上级] from T where [用户名]=@UserName)
    select @UserName=[上级] from T where [用户名]=@UserName
    if @i<=@Lev
    begin
    if @Name is not null
        INSERT INTO @T( 用户名, Lev ) VALUES  ( @Name,@i )
    else 
        INSERT INTO @T( 用户名, Lev ) VALUES  ( '#',@i )
     goto lab
     end
    return
end
goselect * FROM dbo.F_BOM('oh',3)
/*
用户名 Lev
ting 1
she 2
he 3
*/
select * FROM  dbo.F_BOM('oh',7)
/*
用户名 Lev
ting 1
she 2
he 3
you 4
admin 5
# 6
# 7
*/
go--查下級
if object_id('F_BOM2','FN') is not null
    drop function F_BOM2
go
create function F_BOM2(@UserName nvarchar(50),@Lev int=1)
returns @T2 table ([用户名] nvarchar(50),[上级] nvarchar(50),Lev int)
as
begin
    declare @i int,@s nvarchar(1000)
    set @i=1
    declare @T table ([用户名] nvarchar(50),[上级] nvarchar(50),Lev int)
    insert into @T select [用户名],[上级],lev=@i from T where [上级]=@UserName
    while @@rowcount>0 AND @i<=@Lev
    begin
        set @i=@i+1
        insert into @T
        select a.*,@i as Lev 
        from T as a inner join @T as b on a.[上级]=b.[用户名] and b.Lev=@i-1
    end
    INSERT @T2
    Select * from @T where Lev=@Lev
    
    return
end
go
select * FROM dbo.F_BOM2('admin',1)
/*
用户名 上级 Lev
you admin 1
yumen admin 1
log admin 1
*/
select * FROM dbo.F_BOM2('admin',3)
/*
用户名 上级 Lev
fun ai 3
pit he 3
she he 3
pool ling 3
*/

Answer 8

@guanjm
有时候服务器的东西不是说升级就能升级的

Answer 9

@roy_88
先在这里谢谢你，基本需求是这样了
但似乎那天漏了一个要求，我也没想到，因为让这关系搞湖涂了能再满足一下以下关系吗？
就是传入一个整数深度则查询此深度的上下级（这个已经在上面做到了），上面的lev是从1开始（实际上层级也是1开始才有意义），假若现在传0进去，能否查询此用户的所有上层或者下层的人，并且标出lev，像后面那样返回数据集一样