日期:2014-05-17 浏览次数:20571 次
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中第八條'
;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 行受影响) */