多表数据合同
假设有如下3张表
表1 ：
zd1 zd2 (字段名)
001 A
002 B

表2：
zd1 zd3 (字段名)
001 A1
001 A2
002 B1

表3：
zd1 zd4 (字段名)
001 A3
002 B2
002 B3

合同后想得到的新表如下：
zd1 zd2 zd3 zd4
001 A A1 A3
NULL NULL A2 NULL
002 B B1 B2
NULL NULL NULL B3

最好通过存储过程实现。实在不行用程序也行。 没有什么好的思路，望高手给点建议。谢谢！



 

------解决方案--------------------
/*
假设有如下3张表
表1 ：
zd1 zd2 (字段名)
001 A
002 B

表2：
zd1 zd3 (字段名)
001 A1
001 A2
002 B1

表3：
zd1 zd4 (字段名)
001 A3
002 B2
002 B3

合同后想得到的新表如下：
zd1 zd2 zd3 zd4
001 A A1 A3
NULL NULL A2 NULL
002 B B1 B2
NULL NULL NULL B3
*/

go
if OBJECT_ID('T1')is not null
drop table T1
go
create table T1(
zd1 int,
zd2 varchar(2)
)
go
insert T1
select '001','A' union all
select '002','B'

go
if OBJECT_ID('T2')is not null
drop table T2
go
create table T2(
zd1 int,
zd3 varchar(2)
)
go
insert T2
select '001','A1' union all
select '001','A2' union all
select '002','B1'

go
if OBJECT_ID('T3')is not null
drop table T3
go
create table T3(
zd1 int,
zd4 varchar(2)
)
go
insert T3
select '001','A3' union all
select '002','B2' union all
select '002','B3'



;with T
as
(
select T1.* ,T2.zd3,T3.zd4 from T1 
INNER JOIN T2 ON T1.zd1=T2.zd1
INNER JOIN T3 ON T1.zd1=T3.zd1
)
select
case when px=1 then right('000'+ltrim(zd1),3) else null end as zd1,
case when px=1 then right('000'+ltrim(zd2),3) else null end as zd2,
zd3,zd4
from 
(select px=row_number()over(partition by zd1 order by getdate()),* from T)t
--已经实现到这样了,但是还没打到你的目的,让我试试用游标
/*
zd1 zd2 zd3 zd4
001 00A A1 A3
NULL NULL A2 A3
002 00B B1 B2
NULL NULL B1 B3
*/
------解决方案--------------------
if OBJECT_ID('p_sp')is not null
drop proc p_sp
go
create proc p_sp
as
if OBJECT_ID('tempdb..#t') is not null
drop table #T
create table #T(
num int,
zd1 int,
zd2 varchar(2),
zd3 varchar(2),
zd4 varchar(2)
)
insert #T
select row_number()over(order by getdate()) as num,
T1.* ,T2.zd3,T3.zd4 from T1 
INNER JOIN T2 ON T1.zd1=T2.zd1
INNER JOIN T3 ON T1.zd1=T3.zd1 
declare @zd1 int
declare @zd2 varchar(2)
declare @zd3 varchar(2)
declare @zd4 varchar(2)
declare @num int
declare @czd1 int
declare @czd2 varchar(2)
declare @czd3 varchar(2)
declare @czd4 varchar(2)
declare @cnum int
update #T set zd1 = @zd1
,zd2=@zd2,zd3=@zd3,zd4 = @zd4,num = @num
,@zd1 = case when zd1 = @czd1 then NULL else zd1 end 
,@zd2 = case when zd2 = @czd2 then NULL else zd2 end
,@zd3 = case when zd3 = @czd3 then NULL else zd3 end
,@zd4 = case when zd4 = @czd4 then NULL else zd4 end
,@num = case when num = @cnum then NULL else num end
,@czd1 = zd1 
,@czd2 = zd2 
,@czd3 = zd3 
,@czd4 = zd4 
,@cnum = num 

select a.zd1,a.zd2,a.zd3,a.zd4 from #T a where num is not null

exec p_sp


/*
zd1 zd2 zd3 zd4
1 A A1 A3
NULL NULL A2 NULL
2 B B1 B2
NULL NULL NULL B3
*/