Order 是工單號,同一個Order其SN的前4位是相同的,每個Order中有很多個SN這些SN會相互關聯起來,最後組成一個成品,但是我要跟據其中的一個SN把它相關聯的SN都顯示出來!有點像父子關係,像表中
75A0001下面是75A1001和75A2001,75A1001下面是75A3001
所以組合起來就是:
75A0001 75A1001 75A2001 75A3001我提的問題裡面有兩個錯誤的地方!
應該是:
表1
order SN1 SN2
11111 75A1001 75A0001
11111 75A1003 75A0002
11111 75A1002 75A0003
22222 75A2001 75A0001
22222 75A2002 75A0002
22222 75A2003 75A0003
33333 75A3001 75A1001
33333 75A3002 75A1002
33333 75A3003 75A1003
.................
如上面,如何得到如下結果:
SN1 SN2 SN3 SN4
75A0001 75A1001 75A2001 75A3001
75A0002 75A1003 75A2002 75A3003
75A0003 75A1002 75A2003 75A3002
.................
75A0001下面是75A1001和75A2001,75A1001下面是75A3001
所以組合起來就是:
75A0001 75A1001 75A2001 75A3001我提的問題裡面有兩個錯誤的地方!
應該是:
表1
order SN1 SN2
11111 75A1001 75A0001
11111 75A1003 75A0002
11111 75A1002 75A0003
22222 75A2001 75A0001
22222 75A2002 75A0002
22222 75A2003 75A0003
33333 75A3001 75A1001
33333 75A3002 75A1002
33333 75A3003 75A1003
.................
如上面,如何得到如下結果:
SN1 SN2 SN3 SN4
75A0001 75A1001 75A2001 75A3001
75A0002 75A1003 75A2002 75A3003
75A0003 75A1002 75A2003 75A3002
.................
order SN1 SN2
11111 75A1001 75A0001
11111 75A1003 75A0002
11111 75A1002 75A0003
22222 75A2001 75A0001
22222 75A2002 75A0002
22222 75A2003 75A0003
33333 75A3001 75A1001
33333 75A3002 75A1002
33333 75A3003 75A1003
44444 75A4001 75A1001
44444 75A4001 75A1002
44444 75A4001 75A1003如果数据这样的话,结果是不是就要改成下面:SN1 SN2 SN3 SN4 SN5
75A0001 75A1001 75A2001 75A3001 75A4001
75A0002 75A1002 75A2002 75A3002 75A4002
75A0003 75A1003 75A2003 75A3003 75A4003
returns varchar(30)
as
begin
while @int > 0
begin
if not exists(select 1 from t1 where SN1=@child and SN2<>'')
set @child=''
else
select @child=SN2 from t2 where SN1=@child
set @int=@int-1
end
return @child
end
go
select [SN1],dept_parent=dbo.f_getParent(SN2,1) SN2,
dept_parent=dbo.f_getParent(SN2,2) SN3,dept_parent=dbo.f_getParent(SN2,3) SN4,
from t1?????我想到是这样的..不知道有没有更好的方法
11111 75A1002 75A0003--〉
11111 75A1002 75A0002
11111 75A1003 75A0003
To:
Yang_(扬帆破浪)
實在是抱歉,是我輸入數據時的錯誤!
我用你的方法試了一下,但是得到的資料不完全,如上就會得不出 75A3001 的資料!!!
請各位高手幫幫小弟,不甚感激!!!
謝謝各位!!!!!!!!!!!
create table tmp1 (
[order] varchar(10),
SN1 varchar(10),
SN2 varchar(10)
)
insert tmp1 select
'11111', '75A1001', '75A0001'
union all select
'11111', '75A1002', '75A0002'
union all select
'11111', '75A1003', '75A0003'
union all select
'22222', '75A2001', '75A0001'
union all select
'22222', '75A2002', '75A0002'
union all select
'22222', '75A2003', '75A0003'
union all select
'33333', '75A3001', '75A1001'
union all select
'33333', '75A3002', '75A1002'
union all select
'33333', '75A3003', '75A1003'
union all select
'44444', '75A4003', '75A3003' --增加了一条数据go--实现功能
--需要的话你自己改成存储过程--建立结果表
create table #(
SN1 varchar(10),
SN2 varchar(10)
)--插入第一层
insert #
select SN2,SN1
from tmp1 a
where not exists (
select 1 from tmp1
where SN1=a.SN2
)
and not exists (
select 1 from tmp1
where SN2=a.SN2
and [Order]<a.[Order]
)--循环插入以后层
declare @Nsql Nvarchar(4000)
declare @sql varchar(8000)
declare @i int
declare @Cont int
declare @Fields varchar(2000)
set @i=2
set @Cont=0
set @Fields='t.SN1'
while 1=1
begin
set @Nsql=N'select @C= count(*) from # where SN'+cast(@i as varchar)+' is not null'
exec sp_executesql @Nsql,N'@c int output',@Cont output
--没有新插入表示结束
if @Cont=0 goto TheEnd
set @Fields=@Fields+',t.SN'++cast(@i as varchar)
set @i=@i+1
set @sql='alter table # add SN'+cast(@i as varchar)+' varchar(10)'
exec(@sql)
set @sql='while exists(
select 1
from # t,tmp1 a
where a.SN2 in ('+@Fields+')
and t.SN'+cast(@i as varchar)+' is null
and a.SN1 not in ('+@Fields+')
and a.[Order] =(
select min([Order]) from tmp1
where SN2 in ('+@Fields+')
and SN1 not in ('+@Fields+')
)
)
update #
set SN'+cast(@i as varchar)+'=a.SN1
from # t,tmp1 a
where a.SN2 in ('+@Fields+')
and t.SN'+cast(@i as varchar)+' is null
and a.SN1 not in ('+@Fields+')
and a.[Order] =(
select min([Order]) from tmp1
where SN2 in ('+@Fields+')
and SN1 not in ('+@Fields+')
)
'
exec(@sql)
end
TheEnd:
--显示结果
exec ('select '+@Fields+' from # t')--删除临时表
drop table #
go
--删除环境
drop table tmp1--结果
SN1 SN2 SN3 SN4 SN5
---------- ---------- ---------- ---------- ----------
75A0001 75A1001 75A2001 75A3001 NULL
75A0002 75A1002 75A2002 75A3002 NULL
75A0003 75A1003 75A2003 75A3003 75A4003
Yang_(扬帆破浪)
謝謝你幫我解答,還有一個不情之情,可不可以幫我寫成存儲過程,因爲我是新手,謝謝啦!!!
as--建立结果表
create table #(
SN1 varchar(10),
SN2 varchar(10)
)--插入第一层
insert #
select SN2,SN1
from tmp1 a
where not exists (
select 1 from tmp1
where SN1=a.SN2
)
and not exists (
select 1 from tmp1
where SN2=a.SN2
and [Order]<a.[Order]
)--循环插入以后层
declare @Nsql Nvarchar(4000)
declare @sql varchar(8000)
declare @i int
declare @Cont int
declare @Fields varchar(2000)
set @i=2
set @Cont=0
set @Fields='t.SN1'
while 1=1
begin
set @Nsql=N'select @C= count(*) from # where SN'+cast(@i as varchar)+' is not null'
exec sp_executesql @Nsql,N'@c int output',@Cont output
--没有新插入表示结束
if @Cont=0 goto TheEnd
set @Fields=@Fields+',t.SN'++cast(@i as varchar)
set @i=@i+1
set @sql='alter table # add SN'+cast(@i as varchar)+' varchar(10)'
exec(@sql)
set @sql='while exists(
select 1
from # t,tmp1 a
where a.SN2 in ('+@Fields+')
and t.SN'+cast(@i as varchar)+' is null
and a.SN1 not in ('+@Fields+')
and a.[Order] =(
select min([Order]) from tmp1
where SN2 in ('+@Fields+')
and SN1 not in ('+@Fields+')
)
)
update #
set SN'+cast(@i as varchar)+'=a.SN1
from # t,tmp1 a
where a.SN2 in ('+@Fields+')
and t.SN'+cast(@i as varchar)+' is null
and a.SN1 not in ('+@Fields+')
and a.[Order] =(
select min([Order]) from tmp1
where SN2 in ('+@Fields+')
and SN1 not in ('+@Fields+')
)
'
exec(@sql)
end
TheEnd:
--显示结果
exec ('select '+@Fields+' from # t')--删除临时表
drop table #
go
--调用方法
exec pr_linkdata--要把表名tmp1替换成你自己的表名
00000 11111 22222 33333 44444
---------- ---------- ---------- ---------- ----------
75A0001 75A1001 75A2001 75A3001 NULL
75A0002 75A1002 75A2002 75A3002 NULL
75A0003 75A1003 75A2003 75A3003 75A4003
Yang_(扬帆破浪)
首先謝謝你幫我如此費心的解決這個問題,但如上的面的問題還待解決,麻煩你還幫我看一下,如何用Order來表示表頭,第一個肯定沒有Order就用00000來表示,如果分不夠還可再加!眞是謝謝你啦!!!
有困难
倒不是因为语句不好写
而是怕你出现如下数据order SN1 SN2
11111 75A1001 75A0001
11111 75A1002 75A0002
11111 75A1003 75A0003
22222 75A2001 75A0001
22222 75A2003 75A0003
33333 75A3001 75A1001
33333 75A3002 75A1002
33333 75A3003 75A1003不知道要求的结果是(以order表示,那就是个行转列的问题)
00000 11111 22222 33333
---------- ---------- ---------- ----------
75A0001 75A1001 75A2001 75A3001
75A0002 75A1002 null 75A3002
75A0003 75A1003 75A2003 75A3003 还是(以相关性决定,75A3002放在了22222下面,但它的order是33333,这种要求以order表头就有点牵强了)
00000 11111 22222 33333
---------- ---------- ---------- ----------
75A0001 75A1001 75A2001 75A3001
75A0002 75A1002 75A3002 null
75A0003 75A1003 75A2003 75A3003
00000 11111 22222 33333
---------- ---------- ---------- ----------
75A0001 75A1001 75A2001 75A3001
75A0002 75A1002 null 75A3002
75A0003 75A1003 75A2003 75A3003 用函数:
--建立函数
create function fn_Root(
@SN1 varchar(10)
)
returns varchar(10)
as
begin
declare @r varchar(10)
select @r=SN2 from tmp1 where SN1=@SN1
while exists (select 1 from tmp1 where SN1=@r)
select @r=SN2 from tmp1 where SN1=@r
return @r
end
go--建立存储过程
create proc pr_linkdata
asdeclare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when [order]='''+[order]+''' then SN1 else null end) as ['+[order]+']'
from tmp1
group by [order]exec('select dbo.fn_root(SN1) as [00000]'+@sql+' from tmp1 group by dbo.fn_root(SN1)')go--调用
exec pr_linkdata--未测试