大数据学习——hive的sql练习题

create table a(age int) 
row format delimited
fields terminated by ',';

create table b(age int) 
row format delimited
fields terminated by ',';

create table c(age int) 
row format delimited
fields terminated by ',';

a.txt
1
2
3
4
5
6
7
8
9

b.txt
2
3
11
12
14
15
16
18
35
6
7
8

c.txt
1
2
3
11
5
6
7
8
20
30
40

select * from (select a.* from a full outer join b  on a.age=b.age full outer join c on a.age =c.age where  (a.age is not null and b.age is null and c.age is null) or  (b.age is not null and a.age is null and c.age is null) or (c.age is not null and a.age is null and b.age is null) union select b.* from b full outer join a  on a.age=b.age full outer join c on b.age =c.age where (a.age is not null and b.age is null and c.age is null) or  (b.age is not null and a.age is null and c.age is null) or (c.age is not null and a.age is null and b.age is null) union select c.* from c full outer join b  on c.age=b.age full outer join a on a.age =c.age where (a.age is not null and b.age is null and c.age is null) or  (b.age is not null and a.age is null and c.age is null) or (c.age is not null and a.age is null and b.age is null)) d where d.age is not null ;

select age,count(age) as c1 from (select age from a union all select age from b union all select age from c) t_union group by age having c1=1;