create table #T1(FDate datetime, FName nvarchar(10), FInQty int) Insert into #T1(FDate, FName, FInQty) Select '2011-08-01', '豆粕', 80 Insert into #T1(FDate, FName, FInQty) Select '2011-08-15', '豆粕', 100 Insert into #T1(FDate, FName, FInQty) Select '2011-08-02', '玉米', 500 create table #T2(FDate datetime, FName nvarchar(10), FSaleQty int) Insert into #T2(FDate, FName, FSaleQty) Select '2011-08-02', '豆粕', 40 Insert into #T2(FDate, FName, FSaleQty) Select '2011-08-10', '玉米', 10 Insert into #T2(FDate, FName, FSaleQty) Select '2011-08-10', '玉米', 15 Insert into #T2(FDate, FName, FSaleQty) Select '2011-08-12', '玉米', 25 go select a.fname,b.fdate,b.finqty,c.fdate,c.FSaleQty from( select distinct fname,rn from( select row_number()over(partition by fname order by fdate)rn,fname from #T1 union select row_number()over(partition by fname order by fdate)rn,fname from #T2 )t)a left join (select row_number()over(partition by fname order by fdate)rn,* from #T1) b on a.FName =b.FName and b.rn=a.rn left join (select row_number()over(partition by fname order by fdate)rn,* from #T2) c on a.FName =c.FName and c.rn=a.rn order by fname /* fname fdate finqty fdate FSaleQty ---------- ----------------------- ----------- ----------------------- ----------- 豆粕 2011-08-01 00:00:00.000 80 2011-08-02 00:00:00.000 40 豆粕 2011-08-15 00:00:00.000 100 NULL NULL 玉米 2011-08-02 00:00:00.000 500 2011-08-10 00:00:00.000 10 玉米 NULL NULL 2011-08-10 00:00:00.000 15 玉米 NULL NULL 2011-08-12 00:00:00.000 25(5 行受影响)*/ go drop table #t1,#t2
Select FDate ,Case when FID = 1 then FQty End as FInQty ,Case when FID = 21 then FQty End as FOutQtyfrom ( Select 1 as FID, FDate, FName, FInQty as FQty from #T1 Union All Select 21 as FID, FDate, FName, FSaleQty as FQty from #T2 ) t Order by t.FName, t.FDate
Insert into #T1(FDate, FName, FInQty)
Select '2011-08-01', '豆粕', 80
Insert into #T1(FDate, FName, FInQty)
Select '2011-08-15', '豆粕', 100
Insert into #T1(FDate, FName, FInQty)
Select '2011-08-02', '玉米', 500
create table #T2(FDate datetime, FName nvarchar(10), FSaleQty int)
Insert into #T2(FDate, FName, FSaleQty)
Select '2011-08-02', '豆粕', 40
Insert into #T2(FDate, FName, FSaleQty)
Select '2011-08-10', '玉米', 10
Insert into #T2(FDate, FName, FSaleQty)
Select '2011-08-10', '玉米', 15
Insert into #T2(FDate, FName, FSaleQty)
Select '2011-08-12', '玉米', 25
go
select a.fname,b.fdate,b.finqty,c.fdate,c.FSaleQty
from(
select distinct fname,rn from(
select row_number()over(partition by fname order by fdate)rn,fname from #T1
union
select row_number()over(partition by fname order by fdate)rn,fname from #T2
)t)a
left join (select row_number()over(partition by fname order by fdate)rn,* from #T1) b on a.FName =b.FName and b.rn=a.rn
left join (select row_number()over(partition by fname order by fdate)rn,* from #T2) c on a.FName =c.FName and c.rn=a.rn
order by fname
/*
fname fdate finqty fdate FSaleQty
---------- ----------------------- ----------- ----------------------- -----------
豆粕 2011-08-01 00:00:00.000 80 2011-08-02 00:00:00.000 40
豆粕 2011-08-15 00:00:00.000 100 NULL NULL
玉米 2011-08-02 00:00:00.000 500 2011-08-10 00:00:00.000 10
玉米 NULL NULL 2011-08-10 00:00:00.000 15
玉米 NULL NULL 2011-08-12 00:00:00.000 25(5 行受影响)*/
go
drop table #t1,#t2
Select FDate
,Case when FID = 1 then FQty End as FInQty
,Case when FID = 21 then FQty End as FOutQtyfrom
(
Select 1 as FID, FDate, FName, FInQty as FQty from #T1
Union All
Select 21 as FID, FDate, FName, FSaleQty as FQty from #T2
) t
Order by t.FName, t.FDate