两个表数据合并

if object_id('TESTA')is not null  drop table TESTA
if object_id('TESTB') is not null drop table TESTB

create table TESTA
(
recdate smalldatetime,
rmk nvarchar(100)
)
create table TESTB
(
recdate smalldatetime,
rmk nvarchar(100)
)

insert TESTA
select '2012-09-01','A中第一'
union select '2012-09-01','A中第二'
union select '2012-09-02','A中第三'
union select '2012-09-02','A中第四'
union select '2012-09-02','A中第五'
union select '2012-09-02','A中第六'

insert TESTB
select '2012-09-01','B中第一'
union select '2012-09-01','B中第二'
union select '2012-09-01','B中第三'
union select '2012-09-01','B中第四'
union select '2012-09-01','B中第五'
union select '2012-09-02','B中第六'
union select '2012-09-02','B中第七'
union select '2012-09-02','B中第八'

想要的结果：特别说明：rmk 代表很多列，这里简写了
recdate,a.rmk,b.rmk

2012-09-01 00:00:00A中第一B中第一
2012-09-01 00:00:00A中第二B中第二
2012-09-01 00:00:00 B中第三
2012-09-01 00:00:00B中第四
2012-09-01 00:00:00B中第五

2012-09-02 00:00:00A中第三B中第六
2012-09-02 00:00:00A中第四B中第七
2012-09-02 00:00:00A中第五B中第八
2012-09-02 00:00:00A中第六















[解决办法]

;with cet1 as(select *,row_number()over(partition by recdate order by rmk) rn from testa
),
CET2 as(select *,row_number()over(partition by recdate order by rmk) rn from testb)
select isnull(a.recdate, b.recdate) recdate,
isnull(a.rmk, b.rmk) [a.rmk],case when a.rmk is null then '' else isnull(b.rmk, '') end [b.rmk]
 from cet1 a full outer join cet2 b on a.recdate = b.recdate and a.rn = b.rn
/*recdate                 a.rmk      b.rmk
----------------------- -------------------------
2012-09-01 00:00:00     A中第1      B中第1
2012-09-01 00:00:00     A中第2      B中第2
2012-09-01 00:00:00     B中第3      
2012-09-01 00:00:00     B中第4      
2012-09-01 00:00:00     B中第5      
2012-09-02 00:00:00     A中第3      B中第6
2012-09-02 00:00:00     A中第4      B中第7
2012-09-02 00:00:00     A中第5      B中第8
2012-09-02 00:00:00     A中第6      

(9 行受影响)
*/