sql递归的问题

[benniaoyaofei]

数据库结构是
bid p_bid level
bid自动编码,p_bid是从bid取值,level控制层数
bid p_bid level
1 null
2 1
3 1
4 3
5 4
6 5
7 1
8 2
我想让结果显示出来的样子是
1
2
8
3
4
5
6
7
请问应该怎么写sql?
我想写入存储过程,参数是@bid
当@bid=3的时候
就显示
3
4
5
6
利用level控制空格
谢谢了

[中国风]

--测试数据
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 SPACE(b.Level*2)+' '+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort

--Result:
/*
 山东省
   烟台市
     招远市
   青岛市
 四会市
   清远市
     小分市

（所影响的行数为 7 行）


*/

[中国风]

楼主可下载一下邹老大书的代码看看例子...

[fa_ge]

create table t
(bid int,   p_bid      int)
insert into t
select 1,         null  union all
select 2,           1   union all
select 3,           1   union all
select 4,           3   union all
select 5,           4   union all
select 6,           5   union all
select 7,           1   union all
select 8,           2 


create function dbo.aa(@parent int)
returns @t table(p_bid int,bid int,level int,sort varchar(1000))
as
begin
  declare @level int
  set @level=1
  insert into @t
  select p_bid,bid,@level,cast(p_bid as varchar)+cast(bid as varchar) from t where p_bid=@parent
  while @@rowcount>0 
    begin
      set @level=@level+1
      insert into @t
      select a.p_bid,a.bid,@level,b.sort+cast(a.bid as varchar)
      from t a,@t b
      where a.p_bid=b.bid
      and b.level=@level-1
    end
  return
end



select p_bid,space(level*6)+cast(bid as varchar)as bid from dbo.aa(1) order by sort


/*
p_bid       bid                                                                                                                                                                                                                                                              
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1                 2
2                       8
1                 3
3                       4
4                             5
5                                   6
1                 7

(7 row(s) affected)
*/

[JL99000]

主要还是那个函数
样式可通过加空格来实现

[dawugui]

128
13456
这样好点吧.

[fa_ge]

create table t
(bid int,   p_bid      int)
insert into t
select 1,         null  union all
select 2,           1   union all
select 3,           1   union all
select 4,           3   union all
select 5,           4   union all
select 6,           5   union all
select 7,           1   union all
select 8,           2 


create function dbo.aa(@parent int)
returns @t table(p_bid int,bid int,level int)
as
begin
  declare @level int
  set @level=1
  insert into @t
  select p_bid,bid,@level from t where p_bid=@parent
  while @@rowcount>0 
    begin
      set @level=@level+1
      insert into @t
      select a.p_bid,a.bid,@level 
      from t a,@t b
      where a.p_bid=b.bid
      and b.level=@level-1
    end
  return
end

select space(level*5)+cast(bid as varchar) from dbo.aa(3)

/*
     4
          5
               6

(3 row(s) affected)
*/

[昵称被占用了]

level没有值？！

[fa_ge]

http://blog.csdn.net/fa_ge/archive/2007/11/21/1895742.aspx

請看我的blog

[benniaoyaofei]

不好意思,level有值的,对应的
bid p_bid level
1 null 0
2 1 1
3 1 1
4 3 2
5 4 3
6 5 4
7 1 1
8 2 2

[benniaoyaofei]

128
13456
这样好点吧.
------------------------------
1
--2
----8
--3
----4
------5
--------6
--7
要求显示成这样的,因为我想用datagridview显示出树的样式来
数据库里也不是只有这几个值
TO:鶴嘯九天
你的例子实现的结果是
select space(level*5)+cast(bid as varchar) from dbo.aa(1)
2
3
7
8
4
5
6
不能让3,4,5,6联一起
还是谢谢你了

[castlooo]

学习

[ADVANTECH_CYZ]

mark sql

[cch1010]

学习

[幻影时空]

嘿嘿, 这倒是挺复杂的!

[LutzMark]

似乎是骑士值问题