Question

SQL SERVER 两个表联合查询导入一张空表时，数据重复问题。难点，求解答

create table a(a1 int,a2 Varchar(30),a3 int)
insert a
select 1 ,'a1' ,1 union all
select 1 ,'a2' ,1 union all
select 1 ,'a3' ,1 union all
select 2 ,'a4' ,2 union all
select 2 ,'a5' ,2 union all
select 2 ,'a6' ,2 union all
select 3 ,'a7' ,3 union all
select 3 ,'a8' ,3 union all
select 3 ,'a9',3

create table b(b1 int,b2 Varchar(30),b3 int)
insert b
select 1 ,'b1' ,1 union all
select 1 ,'b2',1 union all
select 1 ,'b3' ,1 union all
select 3 ,'b4' ,3 union all
select 3 ,'b5' ,3 union all
select 3 ,'b6' ,3

想得到的C为：
a1 b1 1
a2 b2 1
a3 b3 1
a7 b4 3
a8 b5 3
a9 b6 3
或者
a2 b2 1
a3 b1 1
a1 b3 1
a7 b6 3
a8 b4 3
a9 b5 3
等 只要不重复就行....

Answer 1

先分组排名，在连接查询
select ta.a2,tb.b2,ta.a1
(select a1,a2, row_number() over(PARTITION BY ORDER by field1) as 序号 from a) ta,
(select b1,b2, row_number() over(PARTITION BY ORDER by field1) as 序号 from b) tb
where ta.a1 = tb.b1 and ta.序号 = tb.序号

Answer 2

select a.a2,b.b2,b.b3 from a a join b b on a.a1 = b.b1
即可得到如下结果
a1 b1 1
a2 b1 1
a3 b1 1
a1 b2 1
a2 b2 1
a3 b2 1
a1 b3 1
a2 b3 1
a3 b3 1
a7 b4 3
a8 b4 3
a9 b4 3
a7 b5 3
a8 b5 3
a9 b5 3
a7 b6 3
a8 b6 3
a9 b6 3

Answer 3

select a2,b2,a3 from a
inner join b on a.a1=b.b1