写个函数
create function fn_Materiel(
@Materiel varchar(30)
)
returns @r table (
Layer int,
Materiel varchar(30)
)
as
begin
declare @Layer int
set @Layer=1
insert @r values (@Layer,@Materiel)
while exists (select 1 from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
)
begin
insert @r select @Layer+1,Child from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
set @Layer=@Layer+1
end
return
endgo--调用
select * from dbo.fn_Materiel('A')
create function fn_Materiel(
@Materiel varchar(30)
)
returns @r table (
Layer int,
Materiel varchar(30)
)
as
begin
declare @Layer int
set @Layer=1
insert @r values (@Layer,@Materiel)
while exists (select 1 from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
)
begin
insert @r select @Layer+1,Child from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
set @Layer=@Layer+1
end
return
endgo--调用
select * from dbo.fn_Materiel('A')
解决方案 »
- 对比两个数据库
- 求助下~~
- 求帮忙:外部组件发生异常(ObjectExplorer)
- 这样的数据库应该创建什么索引?怎么创建索引?
- 请问这种情况下怎么分月统计?
- 在触发器中执行时出错的问题
- 存储过程和函数的区别?
- 请问各位大虾:怎么用vc建立sql server的数据库和数据表呀?
- 请问' CONSTRAINT [PK_report_user_role] PRIMARY KEY CLUSTERED '中的几个关键字是什么含义?这条语句起什么作用?
- 临时表问题...懂了给分.......
- 求一高难度存储过程********************************************************
- 请教存储过程
while exists (select 1 from tableA a,@r t
无法解决 equal to 操作的排序规则冲突。
(parent varchar(10),
child varchar(10)
)
go
insert into tableA
select 'A','B'
union all
select 'A','C'
union all
select 'B','D'
union all
select 'B','E'
union all
select 'e','f'
union all
select 'e','g'
go
select * from tablea
gowith tablecte(child,lvl) as
(
select child,2 from tablea where parent='a'
union all
select b.child,lvl+1
from tablecte a join tablea b
on a.child=b.parent
where b.parent in (select parent from tablea)
)
select 'a' as materiel , 1 as layer
union all
select * from tablecte
a 1
B 2
C 2
D 3
E 3
f 4
g 4
A是第一层,A下面有B和C,但B下面有D和E,我想它们显示出来的顺序是:
Layer Materiel
1 A
2 B
3 D
3 E
2 C
if object_id('fn_Materiel') is not null
drop function fn_Materiel
go
create function fn_Materiel(
@Materiel varchar(30)
)
returns @r table (
Layer int,
Materiel varchar(30),
FarterM varchar(300)
)
as
begin
declare @Layer int
set @Layer=1
declare @FarterM varchar(300)
set @FarterM=@Materiel+'|'
insert @r values (@Layer,@Materiel,@FarterM)
while exists (select 1 from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
)
begin
insert @r select @Layer+1,Child,t.FarterM+Child+'|' from tableA a,@r t
where a.Parent=t.Materiel and t.Layer=@Layer
set @Layer=@Layer+1 end
return
endgo--调用
select * from dbo.fn_Materiel('A') order by FarterM,Layer谢谢2楼和5楼朋友的帮忙.
有没有办法解决这问题呢?
因为这是要做在一个繁体(Chinese_Taiwan-Stroke-CI_AS)数据库上的.