数据库中实现二叉树的遍历

1/假设现在有一个表结构如下

 车型    起始站     终点站      票价

依维柯     A          E          200
依维柯     A          D          180
依维柯     A          B          70
依维柯     B          C          60
依维柯     C          E          90
依维柯     C          D          50
依维柯     B          E          120
依维柯     A          C          80

沃尔沃     A          E          260
沃尔沃     A          B          60
沃尔沃     B          E          180

现在要写出SQL查询从起始站A到终点站E的所有可选路线及价格.
查询出的结果如下:

 车型           线路            票价

依维柯          A-E             200
依维柯          A-B-E           70+120
依维柯          A-C-E           80+90
依维柯          A-B-C-E         70+60+90
沃尔沃          A-E             260
沃尔沃          A-B-E           180+60

注意：只有同一种车型才可以互相转车；每条线路必须是可达的，但不能有迂回，也就是说A、B、C、D、E是依次的走，不能反过来走。

 

---

 

--生成测试数据
create table t(车型 varchar(10),起始站 varchar(10),终点站 varchar(10),票价 int)
insert into t select '依维柯','A','E',200
insert into t select '依维柯','A','D',180
insert into t select '依维柯','A','B',70
insert into t select '依维柯','B','C',60
insert into t select '依维柯','C','E',90
insert into t select '依维柯','C','D',50
insert into t select '依维柯','B','E',120
insert into t select '依维柯','A','C',80
insert into t select '沃尔沃','A','E',260
insert into t select '沃尔沃','A','B',60
insert into t select '沃尔沃','B','E',180
go

--创建用户定义函数 ———— @start：起始站;@end：终点站
create function f_getline(@start varchar(10),@end varchar(10))
returns @t table(车型 varchar(10),起始站 varchar(10),终点站 varchar(10),线路 varchar(100),票价 varchar(100),level int)
as
begin
    declare @i int
    set @i = 1
   
    insert into @t
    select
        车型,
        起始站,
        终点站,
        起始站+'-'+终点站,
        票价,
        @i
    from
        t
    where
        起始站=@start
   
    while exists(select 1 from @t a,t b where a.车型=b.车型 and a.终点站=b.起始站 and a.终点站<b.终点站 and a.终点站!=@end and a.level=@i)
    begin
        insert into @t
        select
            a.车型,
            a.起始站,
            终点站 = b.终点站,
            线路   = a.线路 + '-' + b.终点站,
            票价   = a.票价 + '+' + rtrim(b.票价),
            @i + 1
        from
            @t a,
            t b
        where
            a.车型=b.车型
            and
            a.终点站=b.起始站
            and
            a.终点站<b.终点站
            and
            a.终点站!=@end
            and
            a.level = @i
      
        set @i = @i + 1
    end

    delete @t where 终点站 != @end
   
    return
end
go

--执行查询
select 车型,线路,票价 from dbo.f_getline('A','E') order by 车型,level

--输出结果
/*
车型      线路      票价
--------  --------  ----------
沃尔沃    A-E       260
沃尔沃    A-B-E     60+180
依维柯    A-E       200
依维柯    A-B-E     70+120
依维柯    A-C-E     80+90
依维柯    A-B-C-E   70+60+90
*/

--删除测试环境
drop function f_getline
drop table T
go

 

*********************************************

2/刚刚得到一个案例,在数据库中实现二叉树的遍历.  
我的表结构如下:  
ID            LeftID            RightID  
1                  2                    3  
2                  4                    5  
3                  6                  NULL  
4                NULL              NULL  
5                NULL              NULL  
6                NULL              NULL  
如何从一个节点开始分别实现左子树和右子树的遍历?  
 
 
 
---------------------------------------------------------------  
 
--生成测试数据  
Create  table  BOM(ID  int,LeftID  int,RightID  int)  
insert  into  BOM  select  1,2      ,3  
insert  into  BOM  select  2,4      ,5  
insert  into  BOM  select  3,6      ,NULL  
insert  into  BOM  select  4,NULL,NULL  
insert  into  BOM  select  5,NULL,NULL  
insert  into  BOM  select  6,NULL,NULL  
GO  
 
--创建用户定义函数  
create  function  f_getChild(@ID  int,@Type  int)      
returns  @t  table(ID  INT,LeftID  INT,RightID  INT,Level  INT)  
as  
begin  
       declare  @i  int  
       set  @i  =  1  
         
       insert  into  @t    
       select    
               ID,  
               (case  @Type  when  1  then  LeftID    end),  
               (case  @Type  when  2  then  RightID  end),  
               @i    
       from    
               BOM  where  ID  =  @ID  
         
       while  @@rowcount<>0  
       begin  
               set  @i  =  @i  +  1  
                 
               insert  into  @t    
               select    
                       a.ID,a.LeftID,a.RightID,@i    
               from    
                       BOM  a,@t  b    
               where    
                       (a.ID=b.LeftID  or  a.ID=b.RightID)  and  b.Level  =  @i-1  
       end  
         
       return  
end  
go  
 
--执行查询  
select  ID  from  dbo.f_getChild(1,1)  where  ID  !=  1  
--输出结果  
/*  
ID  
----  
2  
4  
5  
*/  
 
select  ID  from  dbo.f_getChild(1,2)  where  ID  !=  1  
--输出结果  
/*  
ID  
----  
3  
6  
*/  
 
 
--删除测试数据  
DROP  FUNCTION  f_getChild  
DROP  TABLE  BOM  
GO  
---------------------------------------------------------------  
 
--生成测试数据  
Create  table  BOM(ID  int,pid  int,flag  int)  --id自身编号，pid父编号,flag:标志是左还是右  
insert  into  BOM  select  1,0,0  
insert  into  BOM  select  2,1,0  --0:是左，1：是右  
insert  into  BOM  select  3,1,1  
insert  into  BOM  select  4,2,0  
insert  into  BOM  select  5,2,1  
insert  into  BOM  select  6,3,0  
insert  into  BOM  select  7,6,0  
GO  
 
--创建用户定义函数  
create  function  f_getChild(@ID  int,@Type  int)    --@type:0是左，1：是右.  
returns  @t  table(ID  int,pid  int,flag  int,Level  INT)  
as  
begin  
       declare  @i  int  
       set  @i  =  1  
         
       insert  into  @t    
       select  ID,pid,flag,@i    from  BOM  where  ID  =  @ID  
         
       while  @@rowcount<>0  
       begin  
               set  @i  =  @i  +  1  
                 
               insert  into  @t    
               select  a.ID,a.pid,a.flag,@i  
               from    
                       BOM  a,@t  b    
               where    
                       a.pid=b.id  and  (a.flag=@type  and  @i=2  or  @i>2)  and  b.Level  =  @i-1  
       end  
         
       return  
end  
go  
 
--执行查询  
select  ID  from  dbo.f_getChild(1,0)    
--输出结果  
/*  
ID  
----  
1  
2  
4  
5  
*/  
 
select  ID  from  dbo.f_getChild(1,1)  
--输出结果  
/*  
ID  
----  
3  
6  
*/  
 
 
--删除测试数据  
DROP  FUNCTION  f_getChild  
DROP  TABLE  BOM  
GO