问题描述

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

解决方案二：

select * from A full join B on A.id=B.id

解决方案三：

select * from table1 join table2 on table1.id=table2.id;