原始表1:
LineNo IdNo IdName
132 0 A
134 132 B
137 132 C
138 137 D
139 132 E
140 0 A1
141 140 B1
142 140 B2
143 141 C1
生成表2是这样的:
LineNO IdNo IdName TIdName
132 0 A A
134 132 B A
137 132 C A
138 137 D A
139 132 E A
140 0 A1 A1
141 140 B1 A1
142 140 B2 A1
143 141 C1 A1表的意思是,原始表1的IdNo是一种层次记录,0为顶层,其它的根据IdNo与LineNo的关联决定其关系,并且可以为每一个分枝记录顶层关系的表2,应该怎样写SQL语句?
LineNo IdNo IdName
132 0 A
134 132 B
137 132 C
138 137 D
139 132 E
140 0 A1
141 140 B1
142 140 B2
143 141 C1
生成表2是这样的:
LineNO IdNo IdName TIdName
132 0 A A
134 132 B A
137 132 C A
138 137 D A
139 132 E A
140 0 A1 A1
141 140 B1 A1
142 140 B2 A1
143 141 C1 A1表的意思是,原始表1的IdNo是一种层次记录,0为顶层,其它的根据IdNo与LineNo的关联决定其关系,并且可以为每一个分枝记录顶层关系的表2,应该怎样写SQL语句?
insert a select 132,0,'A'
union all select 134,132,'B'
union all select 137,132,'C'
union all select 138,137,'D'
union all select 139,132,'E'
union all select 140,0,'A1'
union all select 141,140,'B1'
union all select 142,140,'B2'
union all select 143,141,'C1'
select *from a
select *,tidname=(case when [lineno] between 132 and 139 then 'A' else 'A1' end) from a
我表中的Lineno不是固定的,即是132-139之间可以有两个0层的值的。
insert a select 132,0,'A'
union all select 134,132,'B'
union all select 137,132,'C'
union all select 138,137,'D'
union all select 139,132,'E'
union all select 140,0,'A1'
union all select 141,140,'B1'
union all select 142,140,'B2'
union all select 143,141,'C1'
select *from acreate proc T_Test(@IdNo varchar(10))
as
declare @T table([LineNo] int,IdNo int,IdName varchar(20),T_Name varchar(10),lev int)
declare @lev int
set @lev=1
insert @T select [LineNo],IdNo,IdName,IdName as T_Name,@lev as lev from a where IdNo=@IdNo
while @@rowcount>0
begin
set @lev=@lev+1
insert @T select A.[LineNo],A.IdNo,A.IdName,T.T_Name,@lev as lev from A,(select * from @T where lev=@lev-1) T where A.IdNo=T.[LineNo]
end
select [LineNo],IdNo,IdName,T_Name from @T order by [LineNo]exec T_Test 0
create table test
(
[lineno] varchar(10),
idno varchar(10),
idname varchar(10)
)insert into test select '132','0' , 'A'
insert into test select '134','132' , 'B'
insert into test select '137','132' , 'C'
insert into test select '138','137', 'D'
insert into test select '139','132', 'E'
insert into test select '140','0', 'A1'
insert into test select '141','140', 'B1'
insert into test select '142','140', 'B2'
insert into test select '143','141', 'C1'--语句
select t3.[lineno],t3.idno,t3.idname,t2.idname
from test t2,
(select t1.*,lineno2 = (select max([lineno]) from test where idno = '0' and [lineno] <= t1.[lineno])
from test t1
)t3
where t2.[lineno] = t3.lineno2--结果
132 0 A A
134 132 B A
137 132 C A
138 137 D A
139 132 E A
140 0 A1 A1
141 140 B1 A1
142 140 B2 A1
143 141 C1 A1
你的办法行不通,因为我的排列不一定是这样子的,它是连续两个是0,然后再分其它层的。
returns varchar(50)
as
begin
declare @No1 int
declare @r varchar(50)
set @No1=0
set @r='' select @No1=IdNo,@r=IdName from 原始表1 where LineNo=@No
if @No1<>0
return dbo.fn_GetTopName(@No1)
else
return @r
endgo--调用
select *,dbo.fn_GetTopName(LineNo) as TIdName
from 原始表1--没有测试
returns varchar(50)
as
begin
declare @No1 int
declare @r varchar(50)
set @No1=0
set @r='' select @No1=IdNo,@r=IdName from 原始表1 where [LineNo]=@No
if @No1<>0
return dbo.fn_GetTopName(@No1) return @r
endgo
select *,dbo.fn_GetTopName([LineNo]) as TIdName
from 原始表1
select t1.[lineno], t1.idno, t1.idname,
(case isnull(t2.idno, t1.idno)
when 0 then isnull(t2.idname, t1.idname)
else ( select idname from test where [lineno] = t2.idno)
end) pidno
from test t1 left outer join
test t2
on t1.idno = t2.[lineno]