两个表数据合并

SQL code

if object_id('TESTA')is not null  drop table TESTAif object_id('TESTB') is not null drop table TESTBcreate table TESTA(recdate smalldatetime,rmk nvarchar(100))create table TESTB(recdate smalldatetime,rmk nvarchar(100))insert TESTAselect '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 TESTBselect '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中第六
















[解决办法]

SQL code

;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中第12012-09-01 00:00:00     A中第2      B中第22012-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中第62012-09-02 00:00:00     A中第4      B中第72012-09-02 00:00:00     A中第5      B中第82012-09-02 00:00:00     A中第6      (9 行受影响)*/