请问如何由下列数据
父层 子层
A
A A1
A1 B1
A1 B2
A1 B3
B1 C1
B1 C2
B2 C3
B3 C4
查询得出下列结果
归属 层次 父层 子层
A 0 A
A ├1 A A1
A │├2 A1 B1
A │├2 A1 B2
A │├2 A1 B3
A ││├3 B1 C1
A ││├3 B1 C2
A ││├3 B2 C3
A ││├3 B3 C4
父层 子层
A
A A1
A1 B1
A1 B2
A1 B3
B1 C1
B1 C2
B2 C3
B3 C4
查询得出下列结果
归属 层次 父层 子层
A 0 A
A ├1 A A1
A │├2 A1 B1
A │├2 A1 B2
A │├2 A1 B3
A ││├3 B1 C1
A ││├3 B1 C2
A ││├3 B2 C3
A ││├3 B3 C4
解决方案 »
- 敬请高手指教如何利用触发器记录TEXT型字段的记录变动前的值?
- chuifengde(树上的鸟儿)
- 为甚么这么简单一条语句也会引起死锁或阻塞?
- 如何把SP的结果放到一个临时表上(要求临时表是自动创建的,不要用create table创建)
- 急!!!如何向5000张左右的表插入数据
- 关于在数据库设计时的问题!
- ~~~~~sql server字符数数字~~~~~
- 不小心用导入导出功能将令一个库中的数据全部覆盖了。我该怎样才能恢复过来?
- 如何获取触发器的当前状态!!!
- sql 语句的写法?(高分悬赏第一个高人)
- sql2000
- [求助]SQL Server 默认安装用的是什么字符集(编码)?可以改成UTF-8么?
INSERT INTO @T SELECT '','A'
UNION ALL SELECT 'A','A1'
UNION ALL SELECT 'A1','B1'
UNION ALL SELECT 'A1','B2'
UNION ALL SELECT 'A1','B3'
UNION ALL SELECT 'B1','C1'
UNION ALL SELECT 'B1','C2'
UNION ALL SELECT 'B2','C3'
UNION ALL SELECT 'B3','C4';
DECLARE @A VARCHAR(20)
SET @A='A';
WITH CTE_T(归属,层次,父层,子层)
AS
(SELECT 归属=@A,0,父层,子层
FROM @T
WHERE 子层=@A AND 父层=''
UNION ALL
SELECT 归属=@A,层次=层次+1,A.父层,A.子层
FROM @T A INNER JOIN CTE_T B
ON A.父层=B.子层
)
SELECT * FROM CTE_T--结果
/*
归属 层次 父层 子层
-------------------- ----------- ---------- ----------
A 0 A
A 1 A A1
A 2 A1 B1
A 2 A1 B2
A 2 A1 B3
A 3 B3 C4
A 3 B2 C3
A 3 B1 C1
A 3 B1 C2(9 行受影响)*/
在关键字 'WITH' 附近有语法错误。
INSERT INTO A SELECT '','A'
UNION ALL SELECT 'A','A1'
UNION ALL SELECT 'A1','B1'
UNION ALL SELECT 'A1','B2'
UNION ALL SELECT 'A1','B3'
UNION ALL SELECT 'B1','C1'
UNION ALL SELECT 'B1','C2'
UNION ALL SELECT 'B2','C3'
UNION ALL SELECT 'B3','C4';select * from A
create Function F1(@Z varchar(10))
returns varchar(10)
as
begin
declare @F varchar(10)
select @F=父层 from A where 子层=@Z
while @@rowcount>0
begin
if @F=''
begin
set @F=@Z
break
end
else
select @F=父层,@Z=子层 from A where 子层=@F
end
return @F
end
create Function F2(@Z varchar(10))
returns varchar(10)
as
begin
declare @F varchar(10)
declare @i int
declare @lev int
set @lev=-1
select @F=父层 from A where 子层=@Z
while @@rowcount>0
begin
set @lev=@lev+1
select @F=父层 from A where 子层=@F
end
if @lev=0
set @F='0'
else
begin
set @F=''
set @i=@lev
while (@lev>0)
begin
set @F=@F+'| '
set @lev=@lev-1
end
set @F=RTRIM(@F)+ '-' +cast(@i as varchar)
end
return @F
end select dbo.F1(子层) as 归属,dbo.F2(子层) as 层次,* from A
归属 层次 父层 子层
A 0 NULL A
A |-1 A A1
A | |-2 A1 B1
A | |-2 A1 B2
A | |-2 A1 B3
A | | |-3 B1 C1
A | | |-3 B1 C2
A | | |-3 B2 C3
A | | |-3 B3 C4
B 0 NULL B
C |-1 B D1
C | |-2 D1 E1
C | |-2 D1 E2
C | |-2 D1 E3
C |-1 B D2
C | |-2 D2 E4
C | |-2 D2 E5
C |-1 E5 E7
C |-1 E5 E7
C 0 NULL C
C |-1 C D1
C |-1 C D2
C |-1 C D3
C |-1 C E7
C | |-2 E7 F1