以前看到的例子,可以参考
create TABLE tb([username] varchar(10), [L] varchar(10), [r] varchar(10), [registerDate] datetime)
INSERT INTO tb
SELECT 'a', 'b', 'c', '2006-11-1'
UNION ALL SELECT 'b', 'd', 'e', '2006-11-3'
UNION ALL SELECT 'c', 'f', 'g', '2006-11-30'
UNION ALL SELECT 'd', 'h', null, '2006-11-11'
UNION ALL SELECT 'e', null, null, '2006-11-11'
UNION ALL SELECT 'f', null, null, '2006-11-11'
UNION ALL SELECT 'g', null, null, '2006-11-11'
UNION ALL SELECT 'h', null, null, '2006-11-11'--SELECT * FROM tb
DECLARE @T TABLE(username VARCHAR(10), registerDate DATETIME)
DECLARE @U VARCHAR(10)
DECLARE @Seartype tinyint
--设置查询用户
SET @U = 'a'
DECLARE @C INT
--设置查询类型:0查左子树,1查右子树,2查所有
SET @Seartype = 0
IF @Seartype = 0
--查询左子树
INSERT INTO @T
SELECT L, registerDate FROM tb WHERE username = @U
ELSE IF @Seartype = 1
--查询右子树
INSERT INTO @T
SELECT R, registerDate FROM tb WHERE username = @U
ELSE IF @Seartype = 2
---查询所有
INSERT INTO @T
SELECT L, registerDate FROM tb WHERE username = @U
UNION ALL SELECT R, registerDate FROM tb WHERE username = @USET @C = @@ROWCOUNT
WHILE @C <> 0
BEGIN
INSERT INTO @T
SELECT A.L, A.registerDate FROM
(SELECT L, registerDate FROM tb
WHERE username IN(SELECT username FROM @T) AND L IS NOT NULL)A
LEFT JOIN @T B
ON A.L = B.username AND A.registerDate = B.registerDate
WHERE B.username IS NULL
SET @C = @@ROWCOUNT
INSERT INTO @T
SELECT A.R, A.registerDate FROM
(SELECT R, registerDate FROM tb
WHERE username IN(SELECT username FROM @T) AND R IS NOT NULL)A
LEFT JOIN @T B
ON A.R = B.username AND A.registerDate = B.registerDate
WHERE B.username IS NULL
SET @C = @C + @@ROWCOUNT
ENDdrop table tb
create TABLE tb([username] varchar(10), [L] varchar(10), [r] varchar(10), [registerDate] datetime)
INSERT INTO tb
SELECT 'a', 'b', 'c', '2006-11-1'
UNION ALL SELECT 'b', 'd', 'e', '2006-11-3'
UNION ALL SELECT 'c', 'f', 'g', '2006-11-30'
UNION ALL SELECT 'd', 'h', null, '2006-11-11'
UNION ALL SELECT 'e', null, null, '2006-11-11'
UNION ALL SELECT 'f', null, null, '2006-11-11'
UNION ALL SELECT 'g', null, null, '2006-11-11'
UNION ALL SELECT 'h', null, null, '2006-11-11'--SELECT * FROM tb
DECLARE @T TABLE(username VARCHAR(10), registerDate DATETIME)
DECLARE @U VARCHAR(10)
DECLARE @Seartype tinyint
--设置查询用户
SET @U = 'a'
DECLARE @C INT
--设置查询类型:0查左子树,1查右子树,2查所有
SET @Seartype = 0
IF @Seartype = 0
--查询左子树
INSERT INTO @T
SELECT L, registerDate FROM tb WHERE username = @U
ELSE IF @Seartype = 1
--查询右子树
INSERT INTO @T
SELECT R, registerDate FROM tb WHERE username = @U
ELSE IF @Seartype = 2
---查询所有
INSERT INTO @T
SELECT L, registerDate FROM tb WHERE username = @U
UNION ALL SELECT R, registerDate FROM tb WHERE username = @USET @C = @@ROWCOUNT
WHILE @C <> 0
BEGIN
INSERT INTO @T
SELECT A.L, A.registerDate FROM
(SELECT L, registerDate FROM tb
WHERE username IN(SELECT username FROM @T) AND L IS NOT NULL)A
LEFT JOIN @T B
ON A.L = B.username AND A.registerDate = B.registerDate
WHERE B.username IS NULL
SET @C = @@ROWCOUNT
INSERT INTO @T
SELECT A.R, A.registerDate FROM
(SELECT R, registerDate FROM tb
WHERE username IN(SELECT username FROM @T) AND R IS NOT NULL)A
LEFT JOIN @T B
ON A.R = B.username AND A.registerDate = B.registerDate
WHERE B.username IS NULL
SET @C = @C + @@ROWCOUNT
ENDdrop table tb
/*parent对应父亲结点,child对应儿子结点,如果child is NULL则本结点为叶子结点*/ create table new_tree ( parent varchar(80), child varchar(80))
go
insert new_tree values ( '1','2');
insert new_tree values ( '1','3');
insert new_tree values ( '2','4');
insert new_tree values ( '2','5');
insert new_tree values ( '3','6');
insert new_tree values ( '3','7');
insert new_tree values ( '3','8');
insert new_tree values ( '6','9');
insert new_tree values ( '5','10');
insert new_tree values ( '4','11');
insert new_tree values ( '9','12');
insert new_tree values ( '7',NULL);
insert new_tree values ( '8',NULL);
insert new_tree values ( '10',NULL);
insert new_tree values ( '11',NULL);
insert new_tree values ( '12',NULL);
drop proc proc_new_tree
go
/*@parent 输入根结点标识,@mode为0 则输出为所有子孙记录,否则输出所有叶子结点*/ create proc proc_new_tree (@parent varchar(80),@mode int =0)
as
begin
set nocount on
/*如果不是SQLSERVER2000可以用临时表*/
declare @tmp1 table ( parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp2 table ( parent varchar(80), child varchar(80),trace varchar(256))
declare @tmp3 table ( parent varchar(80), child varchar(80),trace varchar(256))
insert @tmp1 select parent,child,@parent from new_tree where parent = @parent
insert @tmp3 select parent,child,@parent from new_tree where parent = @parent
/*循环的次数等于树的深度*/
while exists(select * from @tmp1 where child is not NULL)
begin
insert @tmp2 select a.parent,a.child , b.trace+'->'+a.parent from new_tree a,@tmp1 b where a.parent = b.child
/*@tmp2表中存本次查询的层次的所有结点*/
delete from @tmp1 where child is not NULL
/*@tmp1表中最终存的是叶子结点*/
insert @tmp1 select * from @tmp2
/*@tmp3表中最保存每次查到的子孙*/
insert @tmp3 select * from @tmp2
delete from @tmp2
end
if @mode =0 select * from @tmp3
else select trace from @tmp1
set nocount off
end
go
proc_new_tree '1',1
godrop table new_tree
go
****************************************************************
第一幕...
嫦娥和七仙女问楼主:楼主,你能把分给shawnwan吗?
楼主:当然,我的分全是他的。
嫦娥和七仙女激动地说:楼主你太伟大了!
接着,她们全部脱光衣服,一个个扑向了楼主。
......从此,楼主过上了被八个仙女共同伺候的性福生活。
****************************************************************
第二幕...
阎王爷问楼主:楼主,你能把分给shawnwan吗?
楼主:不,我才不给他。
阎王爷愤怒的说:今晚来我这报道!
......一周过去了,电线杆子和墙上贴满了寻人启事,楼主因不明
原因失踪。一个月后,有人在一个地窖里发现了楼主的尸体,
身边有一块牌匾,上边写着:有分不给shawnwan的后果。
****************************************************************
从此,“有分就给shawnwan”已成为一个在世上流传已久的佳话