表1: No Qty
----------------------
N1 10
N2 20
N3 30表2: No Step SQty
-----------------------------
N1 A 1
N1 B 2
N3 C 3表3: No Step SQty
-----------------------------
N1 D 4
N2 E 5
N2 F 6要求合并后的表为:
表4: No Step2 SQty2 Step3 SQty3
-------------------------------------------------
N1 A 5 D 4
N1 B 3 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL也就是说,以NO列为基准,把子表的STEP和QTY横排,同一个NO,有多个STEP的另排一行,没有STEP的,以NULL填充。
----------------------
N1 10
N2 20
N3 30表2: No Step SQty
-----------------------------
N1 A 1
N1 B 2
N3 C 3表3: No Step SQty
-----------------------------
N1 D 4
N2 E 5
N2 F 6要求合并后的表为:
表4: No Step2 SQty2 Step3 SQty3
-------------------------------------------------
N1 A 5 D 4
N1 B 3 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL也就是说,以NO列为基准,把子表的STEP和QTY横排,同一个NO,有多个STEP的另排一行,没有STEP的,以NULL填充。
表1: No Qty
----------------------
N1 10
N2 20
N3 30 表2: No Step2 SQty2
-----------------------------
N1 A 1
N1 B 2
N3 C 3 表3: No Step3 SQty3
-----------------------------
N1 D 4
N2 E 5
N2 F 6 要求合并后的表为:
表4: No Step2 SQty2 Step3 SQty3
-------------------------------------------------
N1 A 5 D 4
N1 B 3 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL 也就是说,以NO列为基准,把子表的STEP和QTY横排,同一个NO,有多个STEP的另排一行,没有STEP的,以NULL填充。 2楼给出的帖子,比我的简单,只是普通的一个表的行列转换。我这个问题涉及到多个表的合并与行列转换。
还请大家帮忙解决。
-------------------------------------------------
N1 A 5 D 4
N1 B 3 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL 这2个数是怎么算出来的?
表1: No Qty
----------------------
N1 10
N2 20
N3 30 表2: No Step2 SQty2
-----------------------------
N1 A 1
N1 B 2
N3 C 3 表3: No Step3 SQty3
-----------------------------
N1 D 4
N2 E 5
N2 F 6 要求合并后的表为:
表4: No Step2 SQty2 Step3 SQty3
-------------------------------------------------
N1 A 1 D 4
N1 B 2 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL
-- Author: happyflsytone
-- Version:V1.001
-- Date:2008-09-18 00:04:48
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
;
Create table ta(No nvarchar(2),Qty int)
;
Insert into ta
select 'N1',10 union all
select 'N2',20 union all
select 'N3',30
;
-- Test Data: tb
If object_id('tb') is not null
Drop table tb
;
Create table tb(No nvarchar(2),Step2 nvarchar(1),SQty2 int)
;
Insert into tb
select 'N1','A',1 union all
select 'N1','B',2 union all
select 'N3','C',3
;
-- Test Data: tc
If object_id('tc') is not null
Drop table tc
;
Create table tc(No nvarchar(2),Step3 nvarchar(1),SQty3 int)
;
Insert into tc
select 'N1','D',4 union all
select 'N2','E',5 union all
select 'N2','F',6
;
--Start
Select a.No,b.Step2,b.SQty2,c.step3,c.sqty3
from ta a
left join tb b on a.no = b.no
left join tc c on a.no = c.no--Result:
/*
No Step2 SQty2 step3 sqty3
---- ----- ----------- ----- -----------
N1 A 1 D 4
N1 B 2 D 4
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL(5 行受影响)*/
--End
select a.*,b.Step3,b.SQty3 from tb2 a full join tb3 b on a.No=b.No
insert into tb1 select 'N1',10
insert into tb1 select 'N2', 20
insert into tb1 select 'N3', 30 create table tb2([No] varchar(10), Step2 varchar(10), SQty2 int)
insert into tb2 select 'N1', 'A', 1
insert into tb2 select 'N1', 'B', 2
insert into tb2 select 'N3', 'C' , 3 create table tb3([No] varchar(10), Step3 varchar(10), SQty3 int)
insert into tb3 select 'N1', 'D', 4
insert into tb3 select 'N2', 'E', 5
insert into tb3 select 'N2', 'F', 6
goselect a.[No],b.step2,b.sqty2 into #1
from tb1 a left join tb2 b on a.[No]=b.[No]
go
declare @n int,@no varchar(10)
select top 1 @no=a.[No],@n=
(select count(1) from #1 where [No]=a.[No])-(select count(1) from tb3 where [No]=a.[No]) from #1 a
where (select count(1) from #1 where [No]=a.[No])-(select count(1) from tb3 where [No]=a.[No])<>0
while @n is not null
begin
if @n>0
insert into tb3 select @no,null,null
else
insert into #1 select @no,null,null
set @n=null
select top 1 @no=a.[No],@n=
(select count(1) from #1 where [No]=a.[No])-(select count(1) from tb3 where [No]=a.[No]) from #1 a
where (select count(1) from #1 where [No]=a.[No])-(select count(1) from tb3 where [No]=a.[No])<>0
end
select id=identity(int,1,1),* into #2 from #1 order by [No]
select id=identity(int,1,1),* into #3 from tb3 order by [No]
select a.[No],a.step2,a.sqty2,b.step3,b.sqty3 from #2 a inner join #3 b on a.id=b.idgo
drop table tb1,tb2,tb3,#1,#2,#3
/*
No step2 sqty2 step3 sqty3
---------- ---------- ----------- ---------- -----------
N1 A 1 D 4
N1 B 2 NULL NULL
N2 NULL NULL E 5
N2 NULL NULL F 6
N3 C 3 NULL NULL(5 行受影响)
*/