数据库结构是
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控制空格
谢谢了
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控制空格
谢谢了
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
endselect space(level*5)+cast(bid as varchar) from dbo.aa(3)/*
4
5
6(3 row(s) affected)
*/
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
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联一起
还是谢谢你了
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 行)
*/
(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
endselect 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)
*/