sql語句問題 在線等-Sql聯合查詢的語句求助

Sql聯合查詢的語句求助

定義兩個臨時表，數據是這樣的。
insert into #table1 values (1,22)
insert into #table1 values (1,32)
insert into #table1 values (2,22)
insert into #table2 values(1,55)
insert into #table2 values (2,43)
insert into #table2 values (2,63)
我想要兩個表聯合查詢，結果如下圖：

最佳回答：

 SELECT t1.value1 v1,t1.value2 v2,t2.value1 v3,t2.value2 v4  FROM 
(
select t1.*, ROW_NUMBER() OVER(PARTITION BY value1 order by value1) r1 from #table1 t1
) t1 left join
(
select t2.*, ROW_NUMBER() OVER(PARTITION BY value1 order by value1) r1 from #table2 t2
) t2 on t1.r1=t2.r1 and t1.value1 = t2.value1
union
SELECT t1.value1 v1,t1.value2 v2,t2.value1 v3,t2.value2 v4 FROM 
(
select t2.*, ROW_NUMBER() OVER(PARTITION BY value1 order by value1) r1 from #table2 t2
) t2 left join
(
select t1.*, ROW_NUMBER() OVER(PARTITION BY value1 order by value1) r1 from #table1 t1
) t1 on t1.r1=t2.r1 and t1.value1 = t2.value1 

最佳回答：