查询所有下级用户

mextb1860 2009-01-31 01:42:13

如何用函数和存储过程实现查询下级用户,这个下级用户是无限级的,表结构如何
upid是上级用户的id

id username upid
1 xtb 0
2 ffd 1
3 dfdsf 1
4 eee 2
5 ddd 2
6 ttt 4
7 ttt 4
8 yyy 5
9 yyy 5
10 qqq 3
11 uuu 3
13 lkl 7
14 iuiu 6
15 uiu 8
16 kjkhjk 6
17 ghjghj 6
18 jbnmnbm 7
19 ytryrt 7
20 nghjg 8
21 jhgj 8

...全文

218 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

ws_hgo 2009-02-01

打赏
举报

create table tb(id int,name varchar(10),pid int,px int)

insert into tb values(0,'栏目分类',0,1)

insert into tb values(1,'动物',0,1) 

insert into tb values(2,'视频',0,2) 

insert into tb values(3,'老虎',1,1) 

insert into tb values(4,'狮子',1,2) 

insert into tb values(5,'搞笑',2,1) 

go

create function dbo.GetTree(@ID int)

returns @t_Level table(ID int,Level int)

as 

begin

 DECLARE @Level int 

  SET @Level=1 

  INSERT @t_Level SELECT @ID,@Level 

  WHILE @@ROWCOUNT>0 

  BEGIN 

    SET @Level=@Level+1 

    INSERT @t_Level SELECT a.ID,@Level 

    FROM tb a,@t_Level b 

    WHERE a.PID=b.ID 

    AND b.Level=@Level-1 

  END 

  RETURN 



end



SELECT a.* FROM tb a,GetTree(1) b WHERE a.ID=b.ID 





--测试数据

DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))

INSERT @t SELECT '001',NULL ,'山东省'

UNION ALL SELECT '002','001','烟台市'

UNION ALL SELECT '004','002','招远市'

UNION ALL SELECT '003','001','青岛市'

UNION ALL SELECT '005',NULL ,'四会市'

UNION ALL SELECT '006','005','清远市'

UNION ALL SELECT '007','006','小分市'



--深度排序显示处理

--生成每个节点的编码累计(相同当单编号法的编码)

DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))

DECLARE @Level int

SET @Level=0

INSERT @t_Level SELECT ID,@Level,ID

FROM @t

WHERE PID IS NULL

WHILE @@ROWCOUNT>0

BEGIN

    SET @Level=@Level+1

    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID

    FROM @t a,@t_Level b

    WHERE a.PID=b.ID

        AND b.Level=@Level-1

END



select * from @t_Level

--显示结果

SELECT SPACE(b.Level*3)+'|--'+a.Name

FROM @t a,@t_Level b

WHERE a.ID=b.ID

ORDER BY b.Sort

肥龙上天 2009-02-01

打赏
举报

对以上程序稍微做一下变化就成存储过程了

肥龙上天 2009-02-01

打赏
举报



create table tb(id int,username nvarchar(20),upid int)

insert tb

select 1 ,'xtb' ,0 

union all select 2, 'ffd' ,1 

union all select 3,' dfdsf ' ,1 

union all select 4,' eee ' ,2 

union all select 5 ,'ddd ' ,2 

union all select 6 ,'ttt ' ,4  

union all select 7 ,'ttt ' ,4 

union all select 8,' yyy ' ,5  

union all select 9 ,'yyy ' ,5 

union all select 10 ,'qqq ' ,3

union all select 11 ,'uuu ' ,3 

union all select 13 ,'lkl ' ,7 

union all select 14 ,'iuiu ',6 

union all select 15 ,'uiu ',8 

union all select 16 ,'kjkhjk ',6 

union all select 17 ,'ghjghj ',6 

union all select 18 ,'jbnmnbm', 7 

union all select 19 ,'ytryrt' ,7 

union all select 20 ,'nghjg' ,8 

union all select 21 ,'jhgj' ,8



sql 2000

-------------------------

create function fn_pp(@ID int)

returns @tb table (id int,username nvarchar(50),upid int,[level] int)

as

begin

	declare @level int

	set @level = 1

	insert into @tb select id,username,upid,@level from tb where id = @ID

	while @@rowcount > 0

	begin

		set @level = @level + 1

		insert @tb 

		select b.id,b.username,b.upid,@level from @tb a join tb b on b.upid = a.id and a.[level] = @level - 1

	end

	return 

end



select * from dbo.fn_pp(5)

id          username                                           upid        level

----------- -------------------------------------------------- ----------- -----------

5           ddd                                                2           1

8            yyy                                               5           2

9           yyy                                                5           2

15          uiu                                                8           3

20          nghjg                                              8           3

21          jhgj                                               8           3



(6 row(s) affected)



sql 2005

alter function fn_pp(@ID int)

returns @tb table (id int,username nvarchar(50),upid int)

as

begin

	with tt(id ,username,upid)

	as

	(

		select id,username,upid from tb where id = 5

		union all

		select b.id,b.username,b.upid from tt a join tb b on b.upid = a.id

	)

	insert into @tb select * from tt

return 

end

select * from dbo.fn_pp(5)

id          username                                           upid

----------- -------------------------------------------------- -----------

5           ddd                                                2

8            yyy                                               5

9           yyy                                                5

15          uiu                                                8

20          nghjg                                              8

21          jhgj                                               8



(6 row(s) affected)

dawugui 2009-02-01

打赏
举报

2005中获取该节点及其所有子节点的方法。(好象是小梁子写的.)



--> 生成测试数据: @T

DECLARE @T TABLE (BOM_NO INT,PRD_NO INT,NAME VARCHAR(5))

INSERT INTO @T

SELECT 123456,1234561,'测试1' UNION ALL

SELECT 123456,1234562,'测试2' UNION ALL

SELECT 1234561,1234563,'测试3' UNION ALL

SELECT 1234561,1234564,'测试4' UNION ALL

SELECT 123458,1234581,'测试5' UNION ALL

SELECT 123459,1234591,'测试6' UNION ALL

SELECT 123459,1234592,'测试7' UNION ALL

SELECT 1234564,12345641,'测试8'



--SQL查询如下:



DECLARE @BOM_NO INT

SET @BOM_NO=123456



;WITH Liang AS

(

    SELECT 

        BOM_NO,PRD_NO,NAME,PATH=CAST(NAME AS VARCHAR(MAX))

    FROM @T

    WHERE BOM_NO=@BOM_NO

    UNION ALL

    SELECT

        A.BOM_NO,

        A.PRD_NO,

        A.NAME,

        B.PATH+'->'+A.NAME

    FROM @T AS A

        JOIN Liang AS B

            ON A.BOM_NO=B.PRD_NO

)

SELECT *

FROM Liang



/*

BOM_NO      PRD_NO      NAME  PATH

----------- ----------- ----- ---------------------------------------

123456      1234561     测试1   测试1  

123456      1234562     测试2   测试2  

1234561     1234563     测试3   测试1->测试3    

1234561     1234564     测试4   测试1->测试4    

1234564     12345641    测试8   测试1->测试4->测试8      



(5 行受影响)

*/

dawugui 2009-02-01

打赏
举报

[Quote=引用楼主 mextb1860 的帖子:]
如何用函数和存储过程实现查询下级用户,这个下级用户是无限级的,表结构如何
upid是上级用户的id

id username upid
1 xtb 0
2 ffd 1
3 dfdsf 1
4 eee 2
5 ddd 2
6 ttt 4
7 ttt 4
8 yyy 5
9 yyy 5
10 qqq 3
11 uuu 3
13 lkl 7
14 iuiu 6
15 uiu 8
16 kjkhjk 6
17 ghjghj 6
18 jbnmnbm 7
19 ytryrt 7
20 nghjg 8
21 jhgj 8
[/Quote]



/*

标题：查询指定节点及其所有子节点的函数

作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 

时间：2008-05-12

地点：广东深圳

*/



create table tb(id varchar(3) , pid varchar(3) , name varchar(10))

insert into tb values('001' , null  , '广东省')

insert into tb values('002' , '001' , '广州市')

insert into tb values('003' , '001' , '深圳市')

insert into tb values('004' , '002' , '天河区')

insert into tb values('005' , '003' , '罗湖区')

insert into tb values('006' , '003' , '福田区')

insert into tb values('007' , '003' , '宝安区')

insert into tb values('008' , '007' , '西乡镇')

insert into tb values('009' , '007' , '龙华镇')

insert into tb values('010' , '007' , '松岗镇')

go



--查询指定节点及其所有子节点的函数

create function f_cid(@ID varchar(3)) returns @t_level table(id varchar(3) , level int)

as

begin

  declare @level int

  set @level = 1

  insert into @t_level select @id , @level

  while @@ROWCOUNT > 0

  begin

    set @level = @level + 1

    insert into @t_level select a.id , @level

    from tb a , @t_Level b

    where a.pid = b.id and b.level = @level - 1

  end

  return

end

go



--调用函数查询001(广东省)及其所有子节点

select a.* from tb a , f_cid('001') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

001  NULL 广东省

002  001  广州市

003  001  深圳市

004  002  天河区

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 10 行）

*/



--调用函数查询002(广州市)及其所有子节点

select a.* from tb a , f_cid('002') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

002  001  广州市

004  002  天河区



（所影响的行数为 2 行）

*/



--调用函数查询003(深圳市)及其所有子节点

select a.* from tb a , f_cid('003') b where a.id = b.id order by a.id

/*

id   pid  name       

---- ---- ---------- 

003  001  深圳市

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇



（所影响的行数为 7 行）

*/



drop table tb

drop function f_cid

firecc05 2009-01-31

打赏
举报



--SQL 2000

declare @level 

declare @rs table(id int,username varchar(50),upid int,[level] int)

set @level=0

insert into @rs select id,username,upid,@level from tb where id = 要查的id



while @@rowcount>0

begin

	set @level=@level+1



	insert into @rs 

	select tb.id,tb.username,tb.upid,@level

	from tb inner join @rs where tb.upid = @rs.id and @rs.[level]=@level-1

end



SELECT * FROM @rs



--SQL 2005

WITH rs(id ,username,upid,[level]) AS

(

select id,username,upid, 0 from tb where id = 要查的id

UNION ALL

select tb.id, tb.username, tb.upid, rs.[level]+1

from tb inner join rs where tb.upid = rs.id

)

SELECT * FROM rs

firecc05 2009-01-31

打赏
举报

4楼的语法错误，修改后可以用了。如下



--SQL 2000

declare @level int

declare @rs table(id int,username varchar(50),upid int,[level] int)

set @level=0

insert into @rs select id,username,upid,@level from tb where id = 要查的id



while @@rowcount>0

begin

    set @level=@level+1



    insert into @rs 

    select tb.id,tb.username,tb.upid,@level

    from tb inner join @rs b on tb.upid = b.id and b.[level]=@level-1

end



SELECT * FROM @rs



--SQL 2005

WITH rs(id ,username,upid,[level]) AS

(

select id,username,upid, 0 from tb where id = 要查的id

UNION ALL

select tb.id, tb.username, tb.upid, rs.[level]+1

from tb inner join rs on tb.upid = rs.id

)

SELECT * FROM rs

如果要存储过程，就把代码贴进去好了



--SQL 2005

Create Proc S_GetDowns @id int

AS

WITH rs(id ,username,upid,[level]) AS

(

select id,username,upid, 0 from tb where id = @id

UNION ALL

select tb.id, tb.username, tb.upid, rs.[level]+1

from tb inner join rs on tb.upid = rs.id

)

SELECT id ,username,upid FROM rs



--测试结果

EXEC S_GetDowns 3



id          username                                           upid

----------- -------------------------------------------------- -----------

3           dfdsf                                              1

10          qqq                                                3

11          uuu                                                3



(3 行受影响)

mextb1860 2009-01-31

打赏
举报

有没有存储过程的啊?

mextb1860 2009-01-31

打赏
举报

[Quote=引用 4 楼 firecc05 的回复:]
SQL code
--SQL 2000
declare @level
declare @rs table(id int,username varchar(50),upid int,[level] int)
set @level=0
insert into @rs select id,username,upid,@level from tb where id = 要查的id

while @@rowcount>0
begin
set @level=@level+1

insert into @rs
select tb.id,tb.username,tb.upid,@level
from tb inner join @rs where tb.upid = @rs.id and @rs.[level]=@level-1
e…
[/Quote]

你的用不了啊

子陌红尘 2009-01-31

打赏
举报

以上是SQL Server 2000里的处理方式示例。

子陌红尘 2009-01-31

打赏
举报



--生成测试数据

create table BOM(ID VARCHAR(10),PID VARCHAR(10))

insert into BOM select 'a',NULL

insert into BOM select 'b','a'

insert into BOM select 'c','a'

insert into BOM select 'd','b'

insert into BOM select 'e','b'

insert into BOM select 'f','c'

insert into BOM select 'g','c'

go



--创建用户定义函数

create function f_getChild(@ID VARCHAR(10))

returns varchar(8000)

as

begin

    declare @i int,@ret varchar(8000)

    declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)

    set @i = 1

    insert into @t select ID,PID,@i from BOM where PID = @ID

    

    while @@rowcount<>0

    begin

        set @i = @i + 1

        

        insert into @t 

        select 

            a.ID,a.PID,@i 

        from 

            BOM a,@t b 

        where 

            a.PID=b.ID and b.Level = @i-1

    end

    

    select @ret = isnull(@ret,'')+ID from @t

    

    return @ret

end

go



--执行查询

select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID

go



--输出结果

/*

a    bcdefg

b    de

c    fg

d    

e    

f    

g    

*/



--删除测试数据

drop function f_getChild

drop table BOM

子陌红尘 2009-01-31

打赏
举报





--生成测试数据

create table BOM(ID INT,PID INT,MSG VARCHAR(1000))

insert into BOM select 1,0,NULL

insert into BOM select 2,1,NULL

insert into BOM select 3,1,NULL

insert into BOM select 4,2,NULL

insert into BOM select 5,3,NULL

insert into BOM select 6,5,NULL

insert into BOM select 7,6,NULL

go



--创建用户定义函数

create function f_getChild(@ID VARCHAR(10))

returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)

as

begin

    declare @i int,@ret varchar(8000)

    set @i = 1

    insert into @t select ID,PID,@i from BOM where PID = @ID

    

    while @@rowcount<>0

    begin

        set @i = @i + 1

        

        insert into @t 

        select 

            a.ID,a.PID,@i 

        from 

            BOM a,@t b 

        where 

            a.PID=b.ID and b.Level = @i-1

    end

    return

end

go



--执行查询

select ID from dbo.f_getChild(3)

go



--输出结果

/*

5

6

7

*/



--删除测试数据

drop function f_getChild

drop table BOM