select id=identity(int,1,1) ,io_date=isnull(in_date,out_date) ,in_num=isnull(in_num,0) ,out_num=isnull(out_num,0) into #t from t1 a full join t2 b on a.in_date=b.out_date and( select sum(1) from t1 where in_date=a.in_date and id<=a.id )=( select sum(1) from t2 where out_date=b.out_date and id<=b.id)select * from #t
create table t1( id int identity(1,1), in_date datetime, in_num int )create table t2( id int identity(1,1), out_date datetime, out_num int )insert t1(in_date,in_num) select '2004-4-1',100 union all select '2004-4-1',200 union all select '2004-4-2',300insert t2(out_date,out_num) select '2004-4-1',400 union all select '2004-4-2',500 union all select '2004-4-2',600 goselect id=identity(int,1,1) ,io_date=isnull(in_date,out_date) ,in_num=isnull(in_num,0) ,out_num=isnull(out_num,0) into #t from t1 a full join t2 b on a.in_date=b.out_date and( select sum(1) from t1 where in_date=a.in_date and id<=a.id )=( select sum(1) from t2 where out_date=b.out_date and id<=b.id)select * from #t go drop table t1,t2,#t/*--测试结果 id io_date in_num out_num ----------- --------------------------- ----------- ---------------- 1 2004-04-01 00:00:00.000 100 400 2 2004-04-01 00:00:00.000 200 0 3 2004-04-02 00:00:00.000 300 500 4 2004-04-02 00:00:00.000 0 600(所影响的行数为 4 行) --*/
select x.in_date as io_date, x.in_num y.out_num from ( select *,(select count(*) from t1 where in_date =a.in_date and id<=a.id) as num from t1 a ) as x full join ( select *,(select count(*) from t2 where in_date =a.out_date and id<=a.id) as num from t2 a ) as y on x.in_date=y.out_date and x.num=y.num order by x.in_date,isnull(x.num,y.num)
,io_date=isnull(in_date,out_date)
,in_num=isnull(in_num,0)
,out_num=isnull(out_num,0)
into #t
from t1 a full join t2 b on a.in_date=b.out_date
and(
select sum(1) from t1
where in_date=a.in_date and id<=a.id
)=(
select sum(1) from t2
where out_date=b.out_date and id<=b.id)select * from #t
id int identity(1,1),
in_date datetime,
in_num int
)create table t2(
id int identity(1,1),
out_date datetime,
out_num int
)insert t1(in_date,in_num)
select '2004-4-1',100
union all select '2004-4-1',200
union all select '2004-4-2',300insert t2(out_date,out_num)
select '2004-4-1',400
union all select '2004-4-2',500
union all select '2004-4-2',600
goselect id=identity(int,1,1)
,io_date=isnull(in_date,out_date)
,in_num=isnull(in_num,0)
,out_num=isnull(out_num,0)
into #t
from t1 a full join t2 b on a.in_date=b.out_date
and(
select sum(1) from t1
where in_date=a.in_date and id<=a.id
)=(
select sum(1) from t2
where out_date=b.out_date and id<=b.id)select * from #t
go
drop table t1,t2,#t/*--测试结果
id io_date in_num out_num
----------- --------------------------- ----------- ----------------
1 2004-04-01 00:00:00.000 100 400
2 2004-04-01 00:00:00.000 200 0
3 2004-04-02 00:00:00.000 300 500
4 2004-04-02 00:00:00.000 0 600(所影响的行数为 4 行)
--*/
x.in_date as io_date,
x.in_num
y.out_num
from (
select *,(select count(*) from t1 where in_date =a.in_date and id<=a.id) as num from t1 a
) as x
full join (
select *,(select count(*) from t2 where in_date =a.out_date and id<=a.id) as num from t2 a
) as y
on x.in_date=y.out_date
and x.num=y.num
order by x.in_date,isnull(x.num,y.num)