HQL查询练习

 练习1:

2022-08-07
1,liuyan
2,tangyan
3,jinlian
4,dalang
5,ximenqing

2022-08-08
1,liuyan
2,tangyan
4,dalang
6,wusong

 

-- 创建分区表记录每天用户登陆信息
create table tb_login
(
    uid  int,
    name string
) partitioned by (dt string)
    row format delimited fields terminated by ",";

load data local inpath '/root/2022-08-07' into table tb_login partition (dt = '2022-08-07');
load data local inpath '/root/2022-08-08' into table tb_login partition (dt = '2022-08-08');

 

-- 查询7号和8号都登陆人的信息
select t1.uid, t1.name
from (select * from tb_login where dt = '2022-08-07') t1
         join
         (select * from tb_login where dt = '2022-08-08') t2
         on t1.uid = t2.uid;

-- 查询7号登陆 8号没登录人的信息
select *
from (select * from tb_login where dt = '2022-08-07') t1
         left join
         (select * from tb_login where dt = '2022-08-08') t2
         on t1.uid = t2.uid
where t2.uid is null;
-- 查询8号登陆 7号没登陆人的信息
select t2.*
from (select * from tb_login where dt = '2022-08-07') t1
         right join
         (select * from tb_login where dt = '2022-08-08') t2
         on t1.uid = t2.uid
where t1.uid is null;
-- 查询7号登陆 8号没登陆 和 8号登陆 7号没登陆人的信息
select *
from (select * from tb_login where dt = '2022-08-07') t1
         full join
         (select * from tb_login where dt = '2022-08-08') t2
         on t1.uid = t2.uid
where t1.uid is null
   or t2.uid is null;

 练习2

 建表准备数据

vi mark.txt
liuyan,语文,100
liuyan,数学,99
liuyan,英语,100
tangyan,语文,80
tangyan,数学,98
tangyan,英语,60

create table stu_mark
(
    sname   string,
    subject string,
    score   double
) row format delimited fields terminated by ",";

load data local inpath '/root/mark.txt' into table stu_mark;

select * from stu_mark;

-- 使用case when 查询每个人的语文成绩
SELECT sname, case  subject WHEN '语文' THEN score else 0 END as `语文` FROM stu_mark ;

-- 查询每个人的每科成绩 
SELECT sname,
               case  subject WHEN '语文' THEN score else 0 END as `语文`,
               case  subject WHEN '数学' THEN score else 0 END as `数学`,
               case  subject WHEN '英语' THEN score else 0 END as `英语`
FROM stu_mark ;
-- 得到最终结果
SELECT sname,
               max(case  subject WHEN '语文' THEN score else 0 END) as `语文`,
               max(case  subject WHEN '数学' THEN score else 0 END) as `数学`,
               max(case  subject WHEN '英语' THEN score else 0 END) as `英语`
FROM stu_mark group by sname;