SqlServer中有类似Oralce的connect by 递归查询么？

Question

我想查出一个id的所有父id，在Oracle中是这样写的
Select t.business_id
　　from business_table t connect by t.business_id=prior t.pid start with t.business_id='402885fa17c5d8f70117c5fffb8301223' 
　　
其中business_table表中business_id字段为原始id，pid字段为它的父id，在网上搜SqlServer的递归好多都是建临时表或存储过程，请问可以直接一个sql查询出来么？谢谢！

Answer 1

SQL Server 2000不支持，SQL Server 2005好像可以。

Answer 2

可用cte
；with t as 
(
... 起始条件
union all
... 循环条件
)

Answer 3

SQL Server 2000中，可以参考以下自定义函数：--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i=1
    insert into @t select a.ID,a.PID,@i from BOM a,BOM b where a.ID=b.PID and b.ID=@ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getParent(6)
go--输出结果
/*
ID         
---------- 
5
3
1
*/--删除测试数据
drop function f_getParent
drop table BOM

Answer 4

在SQL Server2005中其实提供了CTE[公共表表达式]来实现递归: 关于CTE的使用请查MSDN Declare @Id Int 
Set @Id = 5;    ---在此修改父节点 With RootNodeCTE(Id,ParentId) 
As 
( 
Select Id,ParentId From BOM Where ParentId In (@Id) 
Union All 
Select BOM.Id,BOM.ParentId From RootNodeCTE 
Inner Join BOM
On RootNodeCTE.Id = BOM.ParentId 
) Select * From RootNodeCTE

Answer 5

再问一下，SqlServer查询当前时间是这样写么？
select dt from CurrentTime

Answer 6

--生成测试数据
create table BOM(ID INT,PID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go--创建用户定义函数
create function f_getParent(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i=1
    insert into @t select a.ID,a.PID,@i from BOM a,BOM b where a.ID=b.PID and b.ID=@ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    return
end
go--执行查询
select ID from dbo.f_getParent(6)
go--输出结果
/*
ID         
---------- 
5
3
1
*/--删除测试数据
drop function f_getParent
drop table BOM
－－－－－－－－－－－－－－－－－－－－－－－－－－－
子陌红尘，我这里的id都不是int型的，是varchar的，select id from dbo.f_getParent('4028858318ef32970118ef4f9b700001')这样写不行的啊

Answer 7

--注意字段类型以及长度的设置create function f_getParent(@ID VARCHAR(40))
returns @t table(ID VARCHAR(40),PID VARCHAR(40),Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i=1
    insert into @t select a.ID,a.PID,@i from BOM a,BOM b where a.ID=b.PID and b.ID=@ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.ID,a.PID,@i 
        from 
            BOM a,@t b 
        where 
            a.ID=b.PID and b.Level = @i-1
    end
    return
end
go

Answer 8

我是这样建的函数：
create function GETPARENT(@ID VARCHAR(50))
returns @t table(ID VARCHAR(50),NAME VARCHAR(50),PID VARCHAR(50),Level INT)
as
begin
    declare @i int,@ret varchar(50)
    set @i=1
    insert into @t select a.business_id,a.business_name,a.pid,@i from business_table a,business_table b where a.business_id=b.pid and b.business_id=@ID
    
    while @@rowcount<>0
    begin
        set @i = @i + 1
        
        insert into @t 
        select 
            a.business_id,a.business_name,a.pid,@i 
        from 
            business_table a,@t b 
        where 
            a.business_id=b.pid and b.Level = @i-1
    end
    return
end
go

Answer 9

报的错是：
消息 248，级别 16，状态 1，过程 f_getParent，第 9 行
varchar 值 '4028858318' 的转换溢出了 int 列。超出了最大整数值。
语句已终止。