两个表数据合龙

两个表数据合并

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中第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 行受影响)*/ 

 

查看更多 下一篇