请教一个有难度的SQL语句!

Question

--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
go--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns varchar(8000)
as
begin
    declare @i int,@ret varchar(8000)
    declare @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
    set @i = 1
    insert into @t select ID,PID,@i from BOM where PID = @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.PID=b.ID and b.Level = @i-1
    end
    
    select @ret = isnull(@ret,'')+ID from @t
    
    return @ret
end
go--执行查询
select ID,isnull(dbo.f_getChild(ID),'') from BOM group by ID
go--输出结果
/*
a    bcdefg
b    de
c    fg
d    
e    
f    
g    
*/--删除测试数据
drop function f_getChild
drop table BOM

Answer 1

--生成测试数据
create table BOM(ID INT,Name VARCHAR(10),PID INT)
insert into BOM select 1,'A',0
insert into BOM select 2,'B',1
insert into BOM select 3,'C',1
insert into BOM select 4,'D',2
insert into BOM select 5,'E',3
insert into BOM select 6,'F',4
insert into BOM select 7,'G',6go--创建用户定义函数
create function f_getChild(@ID INT)
returns @t table(ID INT,PID INT,Level INT)
as
begin
    declare @i int,@ret varchar(8000)
    set @i = 0
    
    insert into @t select ID,PID,@i from BOM where 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.PID=b.ID and b.Level = @i-1
    end
    
    return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果自己看
--删除测试数据
drop function f_getChild
drop table BOM

Answer 2

create table A
(
  ID int,
  [姓名] varchar(10),
  [上级ID] int
)
insert A
select 1,'A',0 union
select 2,'B',1 union
select 3,'C',1 union
select 4,'D',2 union
select 5,'E',3 union
select 6,'F',4 union
select 7,'G',5 
go--创建函数
create function f_nodes(@ID int)
returns varchar(8000)
as
begin
      declare @tb table(ID int,[上级ID] int)
      insert @tb
      select ID,[上级ID] from A where ID=@ID      while @@rowcount>0
      begin
            insert @tb
            select A.ID
                   ,A.[上级ID]
            from A
            join @tb B on A.[上级ID]=B.ID
            where A.ID not in(select ID from @tb)
      end      declare @str varchar(8000)
      set @str=''
      select @str=@str+','+convert(varchar,[ID]) from @tb
      return stuff(@str,1,1,'')
end
go-- 查询示例
select dbo.f_nodes(1) '1的下级'
select dbo.f_nodes(2) '2的下级'
select dbo.f_nodes(3) '3的下级'--删除测试环境
drop function f_nodes
drop table A--结果
/*
1的下级            
-----------------
1,2,3,4,5,6,7（所影响的行数为 1 行）2的下级                 
-----------------------
2,4,6（所影响的行数为 1 行）3的下级                                       
---------------------
3,5,7（所影响的行数为 1 行）
*/

Answer 3

如果搂主希望返回当前ID下的所有字节点的话，可以修改函数返回一个table:
--创建函数
create function f_nodes(@ID int)
returns @tb table(ID int,[姓名] varchar(10),[上级ID] int)
as
begin
      insert @tb
      select ID,[姓名],[上级ID] from A where ID=@ID      while @@rowcount>0
      begin
            insert @tb
            select A.ID
                   ,[姓名]
                   ,A.[上级ID]
            from A
            join @tb B on A.[上级ID]=B.ID
            where A.ID not in(select ID from @tb)
      end
    
      return
end
go--调用
select * from f_nodes(1)  --获取1的所有下级

Answer 4

以下是以字符串格式输出子节点的函数：
-------------------------------------------------------------------
--生成测试数据
create table BOM(ID INT,Name VARCHAR(10),PID INT)
insert into BOM select 1,'A',0
insert into BOM select 2,'B',1
insert into BOM select 3,'C',1
insert into BOM select 4,'D',2
insert into BOM select 5,'E',3
insert into BOM select 6,'F',4
insert into BOM select 7,'G',6
go--创建用户定义函数
create function f_getChild(@ID INT)
returns varchar(8000)
as
begin
    declare @i int,@ret varchar(8000)
    declare @t table(ID INT,PID INT,Level INT)
    set @i = 0
    
    insert into @t select ID,PID,@i from BOM where 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.PID=b.ID and b.Level = @i-1
    end
    
    select @ret = isnull(@ret,'')+','+rtrim(ID) from @t
    set @ret = stuff(@ret,1,1,'')    return @ret
end
go--执行查询
select dbo.f_getChild(2)
go--输出结果
/*
2,4,6,7
*/--删除测试数据
drop function f_getChild
drop table BOM

Answer 5

我有个网页的，有点类似 看有没有帮助<script language="vbscript">
' 目录树结构：
' ┌─ 1
' │  ├─ 2
' │  │  ├─ 4
' │  │     ├─ 5
' │  │     │  ├─ 6
' │  │     │     ├─ 7
' │  │     ├─ 8
' │  │
' │  ├─ 3
' │
' ├─ 9
'
' 数据库表：
' CREATE TABLE [dbo].[xni]
' (
' [ID] [int] IDENTITY (1, 1) NOT NULL ,
' [Moden] [int] NOT NULL 
' ) ON [PRIMARY]
' GO
' SET IDENTITY_INSERT xni ON
' INSERT INTO xni(ID, Moden)
' SELECT 1, 0 union ALL
' SELECT 2, 1 union ALL
' SELECT 3, 1 union ALL
' SELECT 4, 2 union ALL
' SELECT 5, 4 union ALL
' SELECT 6, 5 union ALL
' SELECT 7, 6 union ALL
' SELECT 8, 4 union ALL
' SELECT 9, 0
' SET IDENTITY_INSERT xni OFF
'
' 要求：求出某一节点的父节点及该父节点的所有子节点,如7，结果为：7,6,5,4,8,2,1,3 Dim objConn,objRst,strSQL,NumList
Set objConn= CreateObject("ADODB.Connection") 
objConn.Open "Provider=SQLOLEDB;Data Source=127.0.0.1;Initial Catalog=test;UID=sa;PWD=sa;"
On Error Resume Next
NumList = ""
DescList 9
objConn.Close
Set objConn = Nothing
If Left(NumList,1)="," Then NumList = Right(NumList,Len(NumList)-1)
If Right(NumList,1)="," Then NumList = Left(NumList,Len(NumList)-1)
Document.Writeln NumList
If Err Then MsgBox Err.Description Function DescList(ID)
Dim objRst
strSQL = "SELECT Moden FROM xni WHERE ID = "&ID
Set objRst = objConn.Execute(strSQL)
While not objRst.eof
If not instr(1,NumList&",",","&ID&",")>0 Then NumList=NumList&","&ID
AscList(ID)
DescList(objRst("Moden"))
objRst.MoveNext
Wend
objRst.Close
Set objRst=Nothing
End Function Function AscList(ID)
Dim objRst,i
strSQL = "SELECT ID FROM xni WHERE Moden = "&ID
Set objRst=objConn.Execute(strSQL)
While not objRst.eof
If not instr(1,NumList&",",","&objRst("ID")&",")>0 Then NumList=NumList&","&objRst("ID")
AscList(objRst("ID"))
objRst.MoveNext
Wend
objRst.Close
Set objRst=Nothing
End Function
</script>