用一个树读取数据库内容显示父子关系
一个表:
NAME ParentID ID
A 0 1
B 1 2
C 2 3
B 0 2
C 4 3
NULL NULL NULL三个字段 NAME:名称 ParentID:父节点 ID:子节点 我想显示的效果关系是
A
-B
-C
B
-C
如果按我上面的做法 Tree会报错,指定ID要唯一 ,ID是对应NAME的
请问如何实现 上面所列的A,B,C的关系 显示在一个tree里
本人新手,希望高手不吝赐教PS:解决了我会另开个帖子追加100 这个一次最多就能给100了,囧
一个表:
NAME ParentID ID
A 0 1
B 1 2
C 2 3
B 0 2
C 4 3
NULL NULL NULL三个字段 NAME:名称 ParentID:父节点 ID:子节点 我想显示的效果关系是
A
-B
-C
B
-C
如果按我上面的做法 Tree会报错,指定ID要唯一 ,ID是对应NAME的
请问如何实现 上面所列的A,B,C的关系 显示在一个tree里
本人新手,希望高手不吝赐教PS:解决了我会另开个帖子追加100 这个一次最多就能给100了,囧
/*
标题:SQL SERVER 2000中树查询显示层次
作者:爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开)
时间:2010-02-4
地点:新疆乌鲁木齐
*/create table tb(id int , pid int , name varchar(10))
insert into tb values(1 , 0 , '广东省')
insert into tb values(2 , 1 , '广州市')
insert into tb values(3 , 1 , '深圳市')
insert into tb values(4 , 2 , '天河区')
insert into tb values(5 , 3 , '罗湖区')
insert into tb values(6 , 3 , '福田区')
insert into tb values(7 , 3 , '宝安区')
insert into tb values(8 , 7 , '西乡镇')
insert into tb values(9 , 7 , '龙华镇')
insert into tb values(10 ,7 , '松岗镇')
gocreate function f_getnum(@id int) returns varchar(4000)
as
begin
declare @ret varchar(4000) , @pid int
set @ret = right('0000' + rtrim(@id) , 4)
while exists(select 1 from tb where id = @id and pid <> 0 )
begin
select @pid = pid from tb where id = @id and pid <> 0
set @id = @pid
set @ret = right('0000' + rtrim(@id) , 4) + @ret
end
return @ret
end
goselect id , name , REPLICATE('-' , len(dbo.f_getnum(id))/4 - 1) + name as name from tb order by dbo.f_getnum(id)drop function f_getNum
drop table tb/*
id name name
----------- ---------- ---------------
1 广东省 广东省
2 广州市 -广州市
4 天河区 --天河区
3 深圳市 -深圳市
5 罗湖区 --罗湖区
6 福田区 --福田区
7 宝安区 --宝安区
8 西乡镇 ---西乡镇
9 龙华镇 ---龙华镇
10 松岗镇 ---松岗镇(所影响的行数为 10 行)*/
create table tb (id int , Name varchar(10) , pid int )
insert into tb values(1 ,'广东省' , 0)
insert into tb values(2 ,'四川省' , 0)
insert into tb values(3 ,'湖北省' , 0)
insert into tb values(4 ,'东莞市' , 1)
insert into tb values(5 ,'广州市' , 1)
insert into tb values(6 ,'天河区' , 5)
insert into tb values(7 ,'绵阳市' , 2)
insert into tb values(8 ,'武汉市' , 3)
insert into tb values(9 ,'汉口区' , 8)
insert into tb values(10,'随州市' , 3)
gocreate function f_getnum(@id int) returns varchar(4000)
as
begin
declare @ret varchar(4000) , @pid int
set @ret = right(' ' + rtrim(@id) , 4)
while exists(select 1 from tb where id = @id and pid <> 0 )
begin
select @pid = pid from tb where id = @id and pid <> 0
set @id = @pid
set @ret = right(' ' + rtrim(@id) , 4) + @ret
end
return @ret
end
goselect id , name , REPLICATE(' ' , len(dbo.f_getnum(id))/4 - 1) + name as name from tb order by dbo.f_getnum(id)drop function f_getNum
drop table tb/*
id name name
----------- ---------- --------------
1 广东省 广东省
4 东莞市 东莞市
5 广州市 广州市
6 天河区 天河区
2 四川省 四川省
7 绵阳市 绵阳市
3 湖北省 湖北省
8 武汉市 武汉市
9 汉口区 汉口区
10 随州市 随州市(所影响的行数为 10 行)
*/
insert into tb values('A', 0 ,1)
insert into tb values('B', 1 ,2)
insert into tb values('C', 2 ,3)
insert into tb values('B', 0 ,4) --你这里貌似不对
insert into tb values('C', 4 ,5) --你这里貌似不对
go
create function f_getnum(@id int) returns varchar(4000)
as
begin
declare @ret varchar(4000) , @pid int
set @ret = right('----' + rtrim(@id) , 4)
while exists(select 1 from tb where id = @id and pid <> 0 )
begin
select @pid = pid from tb where id = @id and pid <> 0
set @id = @pid
set @ret = right('----' + rtrim(@id) , 4) + @ret
end
return @ret
end
goselect id , name , REPLICATE('--' , len(dbo.f_getnum(id))/4 - 1) + name as name from tb order by dbo.f_getnum(id)drop function f_getNum
drop table tb/*
id name name
----------- ---------- ---------
1 A A
2 B --B
3 C ----C
4 B B
5 C --C(所影响的行数为 5 行)*/
A
-B
-C
B
-C 这样
也就是说,B既可以是A的子节点,也可能和A平级,但他一直是C的父节点
GO
CREATE TABLE TB(NAME VARCHAR(10), ParentID INT, ID INT)
INSERT INTO TB
SELECT 'A', 0, 1 UNION ALL
SELECT 'B', 1, 2 UNION ALL
SELECT 'C', 2, 3 UNION ALL
SELECT 'B', 0, 2 UNION ALL
SELECT 'C', 2, 3 ;WITH MU AS (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS NID,* FROM TB )
,MU2 AS (
SELECT (SELECT COUNT(1) FROM MU T2 WHERE T2.ParentID=0 AND T2.NID<=T1.NID) AS NID2,* FROM MU T1
)
,MU3 AS (
SELECT CONVERT(VARCHAR(8000),MU2.NAME) AS NNAME,* FROM MU2 WHERE ParentID=0
UNION ALL
SELECT REPLICATE(' ',LEN(NNAME))+'-'+MU2.NAME ,MU2.* FROM MU2
INNER JOIN MU3 ON MU2.ParentID=MU3.ID AND MU2.NID2=MU3.NID2
)
SELECT NNAME FROM MU3
ORDER BY NID2
/*
A
-B
-C
B
-C
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(NAME VARCHAR(10), ParentID INT, ID INT)
INSERT INTO TB
SELECT 'A', 0, 1 UNION ALL
SELECT 'B', 1, 2 UNION ALL
SELECT 'C', 2, 3 UNION ALL
SELECT 'B', 0, 2 UNION ALL
SELECT 'C', 2, 3 ;WITH MU AS (SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS NID,* FROM TB )
,MU2 AS (
SELECT (SELECT COUNT(1) FROM MU T2 WHERE T2.ParentID=0 AND T2.NID<=T1.NID) AS NID2,* FROM MU T1
)
,MU3 AS (
SELECT CONVERT(VARCHAR(8000),MU2.NAME) AS NNAME,* FROM MU2 WHERE ParentID=0
UNION ALL
SELECT REPLICATE(' ',LEN(NNAME))+'-'+MU2.NAME ,MU2.* FROM MU2
INNER JOIN MU3 ON MU2.ParentID=MU3.ID AND MU2.NID2=MU3.NID2
)
SELECT NNAME FROM MU3
ORDER BY NID2
/*
A
-B
-C
B
-C
*/
create table t(name varchar(10),pid int,ID int)
insert into t values('A', 0 ,1)
insert into t values('B', 1 ,2)
insert into t values('C', 2 ,3)
insert into t values('B', 0 ,2)
insert into t values('C', 2 ,3)with t1 as
(
select distinct id,name,pid from t
)
,t2 as
(
select groupid = row_number() over(order by getdate()),cn=0,id,name from t1 where pid = 0
union all
select groupid = b.groupid*100+row_number() over(order by getdate()),b.cn+1,a.id,a.name from t1 a join t2 b on a.pid = b.id
)select case cn when 0 then '' else replicate(' ',cn)+'-' end + name from t2
order by ltrim(groupid)+replicate('0',len(groupid))/*
-------------
A
-B
-C
B
-C
*/