问一条SQl语句实现

Question

--生成测试数据
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_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
    declare @i 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
    return
end
go--执行查询
select ID from dbo.f_getChild(3)
go--输出结果
/*
ID
----
5
6
7
*/--删除测试数据
drop function f_getChild
drop table BOM

Answer 1

部门表 Department 字段( ID ,DepartmentName , ParentDepartment) 
员工表 Employee   字段( ID ,EmployeeName , ParentID ) 
create function wsp(@ID VARCHAR(10))
returns varchar(500)
as
begin
    declare @i int
    declare @t table(ID int,ParentDepartment int,Level INT)
    set @i = 1
    insert into @t select ID,ParentDepartment,@i from Department where ID = @ID
    while @@rowcount<>0
    begin
        set @i = @i + 1
        insert into @t select a.ID,a.ParentDepartment,@i 
        from Department a,@t b where a.ParentDepartment=b.ID and b.Level = @i-1
    end
    declare @sql varchar(500)
    select @sql=isnull(@sql+',','')+ltrim(id) from @t 
    return @sql
end
go--调用：select * from Employee where charindex(','+ltrim(ParentID)+',',','+dbo.wsp(部门id)+',')>0

Answer 2

--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb 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','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM tb a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/

Answer 3

create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1) 
insert into tb values(2 , '视频' , 0 , 2) 
insert into tb values(3 , '老虎' , 1 , 1) 
insert into tb values(4 , '狮子' , 1 , 2) 
insert into tb values(5 , '搞笑' , 2 , 1) 
go--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @t_Level SELECT a.ID,@Level 
    FROM tb a,@t_Level b 
    WHERE a.PID=b.ID 
    AND b.Level=@Level-1 
  END 
  RETURN 
END 
GO --调用函数查询id = 1及其所有子节点 
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID 
/*
id          name       pid         px          
----------- ---------- ----------- ----------- 
1           动物         0           1
3           老虎         1           1
4           狮子         1           2
（所影响的行数为 3 行）
*/drop table tb
drop function dbo.f_cid

Answer 4

Create Table department
(departmenid Int,
parentid Int)
Insert department Select 60, null
Union All Select 1, 0
Union All Select 2, 1
Union All Select 3, 1
Union All Select 4, 2
Union All Select 5, 2
Union All Select 6, 3
Union All Select 7, 3
Union All Select 8, 7
GO
--建立函數
Create Function F_GetParent(@departmenid Int)
Returns @Tree Table (departmenid Int, parentid Int)
As
Begin
Insert @Tree Select * From department Where departmenid = @departmenid
While @@Rowcount > 0
Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not Null
Return
End
GO
--測試
Select departmenid From dbo.F_GetParent(8) Order By parentid
GO
--刪除測試環境
Drop Table department
Drop Function F_GetParent