表User结构
UserId UserName ParentName
1 aa 0
2 aa1 aa
3 aa2 aa1
4 aa3 aa2
5 aa4 aa3
6 bb1 aa4
7 bb2 bb1
8 bb3 bb2
9 bb4 bb3
10 bb5 bb4
......ParentName是父用户的名字(0代表顶级用户,没有父用户),现在已知用户名aa,如何获取它下面的所有子用户,并且显示每个用户的级别,比如aa1是aa的直接子用户,显示为1级,aa2是2级,aa3是3级,以此类推。。
UserId UserName ParentName
1 aa 0
2 aa1 aa
3 aa2 aa1
4 aa3 aa2
5 aa4 aa3
6 bb1 aa4
7 bb2 bb1
8 bb3 bb2
9 bb4 bb3
10 bb5 bb4
......ParentName是父用户的名字(0代表顶级用户,没有父用户),现在已知用户名aa,如何获取它下面的所有子用户,并且显示每个用户的级别,比如aa1是aa的直接子用户,显示为1级,aa2是2级,aa3是3级,以此类推。。
返回树的层级关系:--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t 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','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
|--烟台市
|--招远市
|--青岛市
|--四会市
|--清远市
|--小分市
--*/
--生成测试数据
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
declare @User table([UserId] int,[UserName] nvarchar(3),[ParentName] nvarchar(3))
Insert @User
select 1,N'aa',N'0' union all
select 2,N'aa1',N'aa' union all
select 3,N'aa2',N'aa1' union all
select 4,N'aa3',N'aa2' union all
select 5,N'aa4',N'aa3' union all
select 6,N'bb1',N'aa4' union all
select 7,N'bb2',N'bb1' union all
select 8,N'bb3',N'bb2' union all
select 9,N'bb4',N'bb3' union all
select 10,N'bb5',N'bb4'
;with T as
(
Select *,lev=1 from @User where [ParentName]='0'
union all
Select b.*,lev=t.lev+1 from T join @User b on t.[UserName]=b.[ParentName] )
select * from T(10 個資料列受到影響)
UserId UserName ParentName lev
----------- -------- ---------- -----------
1 aa 0 1
2 aa1 aa 2
3 aa2 aa1 3
4 aa3 aa2 4
5 aa4 aa3 5
6 bb1 aa4 6
7 bb2 bb1 7
8 bb3 bb2 8
9 bb4 bb3 9
10 bb5 bb4 10(10 個資料列受到影響)
if object_id('[pruduct]') is not null drop table [pruduct]
create table [pruduct] (part_id varchar(8),name varchar(17))
insert into [pruduct]
select '123456','IBM笔计本电脑整机' union all
select '80','笔计本电脑' union all
select '80.01','笔计本配件组' union all
select '80.01.01','主板' union all
select '80.01.02','内存' union all
select '80.02','液晶屏' union all
select '81','电脑包'
go--> 测试数据: [bom]
if object_id('[bom]') is not null drop table [bom]
create table [bom] (part_id varchar(8),parent_id varchar(8),qty int)
insert into [bom]
select '80','123456',1 union all
select '80.01','80',1 union all
select '80.01.01','80.01',1 union all
select '80.01.02','80.01',2 union all
select '80.02','80',1 union all
select '81','123456',1
godrop function fn_order
go
create function fn_order(@pid varchar(10),@no varchar(20))
returns @t table(no varchar(20),pid varchar(10),name varchar(20),qty int)
as
begin
declare @part_id varchar(10),@name varchar(20),@qty int,@level int,@oid varchar(20)
set @level=1
declare c cursor for select a.part_id,name,qty from bom a,pruduct b where a.part_id=b.part_id and a.parent_id=@pid order by a.part_id
open c
fetch c into @part_id,@name,@qty
while @@fetch_status=0 begin
if @no is null
set @oid=ltrim(@level)
else
set @oid=@no+'.'+ltrim(@level) insert into @t values (@oid,@part_id,@name,@qty)
insert into @t select * from fn_order(@part_id,@oid)
set @level=@level+1
fetch c into @part_id,@name,@qty
end
close c
deallocate c
return
endselect * from fn_order('123456',null)
/*
1 80 笔计本电脑 1
1.1 80.01 笔计本配件组 1
1.1.1 80.01.01 主板 1
1.1.2 80.01.02 内存 2
1.2 80.02 液晶屏 1
2 81 电脑包 1*/
运行你提供的代码提示:在关键字 'with' 附近有语法错误。