HIVE SQL 关联查找总结

创建表1:

create table testdb1(id int,name string, age int, pdate string)

创建表2:

create table testdb2(id int,name string, pdate string)

依次插入数据库成员：

insert into testdb1 values (1,"Alice",24,"20210529")
insert into testdb1 values (2,"Bob",16,"20210529")
insert into testdb1 values (3,"Tom",23,"20210529")
insert into testdb1 values (4,"Lili",21,"20210529")
insert into testdb2 values (1,"Alice","20210529")
insert into testdb2 values (3,"Bob","20210529")
insert into testdb2 values (6,"Zidane","20210529")

两个表的内容如下：

testdb1.id	testdb1.name	testdb1.age	testdb1.pdate
1	Alice	24	20210529
4	Lili	21	20210529
2	Bob	16	20210529
3	Tom	23	20210529

testdb2.id	testdb2.name	testdb2.pdate
6	Zidane	20210529
1	Alice	20210529
3	Bob	20210529

如果我们希望查找name相同的数据并合并两个数据库的信息，则可以通过关联的方法，通常使用inner join 的方法：

select *
FROM
testdb1 a inner join testdb2 b
on
a.name = b.name

这里需要注意，on后面的条件判断必须添加别名（这里是a和b）来实现，如果用表名本身将报错不会被识别。

结果如下：

a.id	a.name	a.age	a.pdate	b.id	b.name	b.pdate
1	Alice	24	20210529	1	Alice	20210529
2	Bob	16	20210529	3	Bob	20210529

即inner join的方法会将右边的数据表根据判断条件，如果判定成功就会拼接到左边的数据库。如果判断条件重复会怎么样呢，我们再插入一个元素：

insert into testdb2 values (4,"Bob","20210529")

此时testdb2的内容为：

testdb2.id	testdb2.name	testdb2.pdate
6	Zidane	20210529
1	Alice	20210529
4	Bob	20210529
3	Bob	20210529

再次执行关联查找后结果如下：

a.id	a.name	a.age	a.pdate	b.id	b.name	b.pdate
1	Alice	24	20210529	1	Alice	20210529
2	Bob	16	20210529	3	Bob	20210529
2	Bob	16	20210529	4	Bob	20210529

可以看到对于重复的情况会重复多行展示，如果在testb1中再插入一条

insert into reco.testdb1 values (5,"Bob",19,"20210529")

关联查询结果如下：

a.id	a.name	a.age	a.pdate	b.id	b.name	b.pdate
1	Alice	24	20210529	1	Alice	20210529
2	Bob	16	20210529	3	Bob	20210529
2	Bob	16	20210529	4	Bob	20210529
5	Bob	19	20210529	3	Bob	20210529
5	Bob	19	20210529	4	Bob	20210529

可以看到会列举所有的情况进行合并。

去重复问题，那么此时会有问题，如果对于重复字段如果只想保留一个那么该如何操作呢？这里我们需要用到SQL中的distinct的功能来实现：

select distinct(a.name)
FROM
testdb1 a inner join testdb2 b
on
a.name = b.name

注意这里的执行顺序，由于会先执行关联，因此字段名要写成a.name。

下面继续给一个3个表的例子：

insert into testdb1 values (1,"Alice",24,"20210529")
insert into testdb1 values (2,"Bob",16,"20210529")
insert into testdb1 values (3,"Tom",23,"20210529")
insert into testdb1 values (4,"Lili",21,"20210529")
insert into testdb2 values (1,"Alice",21,"20210529")
insert into testdb2 values (3,"Bob",22,"20210529")
insert into testdb2 values (6,"Tom",23,"20210529")
insert into testdb3 values (1,"Alice",24,"20210529")
insert into testdb3 values (2,"Bob",16,"20210529")
insert into testdb3 values (4,"Lili",21,"20210529")

关联查找如下：

select
*
from
testdb1 a inner join testdb2 b
on
a.name = b.name
inner join reco.testdb3 c
on
a.name = c.name

保留字段可以得到：

select
a.id as id,
a.name as name,
a.age as age
from
testdb1 a inner join testdb2 b
on
a.name = b.name
inner join testdb3 c
on
a.name = c.name