SQL的场景题分为八个小部分，每一个是5题。
36、需求如下：千锋好程序大数据学院
有用户表user(uid,name) 以及黑名单BanUser(uid) 1. 用left join 方式写sql查出所有不在黑名单的用户信息 2. 用not exists 方法写sql查询所有不在黑名单的用户信息
create table u( id string, name string ) row format delimited fields terminated by ',' ; create table banuser( id string ); load data local inpath '/hivedata/banuser.txt' overwrite into table banuser; load data local inpath '/hivedata/u.txt' overwrite into table u;
分析：l eft join 方式写sql查出所有不在黑名单的用户信息 select u.*, from u left join banuser on u.id = banuser.id where banuser.id is null; 分析：2 用not exists 方法写sql查询所有不在黑名单的用户信息 select u.*, from u where not exists(select 1 from banuser where banuser.id =u.id);
37、需求如下:
course_score表数据：
1,zhangsan,数学,80,2015 2,lisi,语文,90,2016 3,lisi,数学,70,2016 4,wangwu,化学,80,2017 5,zhangsan,语文,85,2015 6,zhangsan,化学,90,2015
create table course_score( id string, name string, course string, score int, year string ) row format delimited fields terminated by ',' ; load data local inpath './hivedata/course_score.txt' overwrite into table course_score;
1、查出每个学期每门课程最高分记录（包含全部5个字段）
select id,name,course,score,year,max(score) over(partition by year,course) from course_score; select year,course,max(score) from course_score group by year,course order by year;
2、查出单个学期中语文课在90分以上的学生的数学成绩记录（包含全部字段）
select A.id,A.name,A.course,A.score,A.year from course_score A join course_score B on A.name = B.name where B.course='语文' and B.score>=90 and A.course='数学';
38、需求如下
t1表：
name course score aa English 75 bb math 85 aa math 90 create table t1_1( name string, course string, score int ) row format delimited fields terminated by ' '; load data local inpath './hivedata/t1_1.txt' overwrite into table t1_1;
使用hql输出以下结果
name English math aa 75 90 bb 0 85
select name, max(if(course="English",score,0)) English, max(id(course="math",score,0)) math from t1_1 froup by name;
39、需求如下千锋好程序大数据学院
t1表：
用户 商品 A P1 B P1 A P2 B P3
请你使用hql变成如下结果:
用户 P1 P2 P3 A 1 1 0 B 1 0 1
select username, max(if(product='p1',1,0)) p1 max(if(product='p2',1,0)) p2 max(if(product='p3',1,0)) p3 from t1 group by username;
40、需求如下
dpt部门
dpt_id dpt_name 1 产品 2 技术
User用户表
User_id Dpt_id 1 1 2 1 3 2 4 2 5 3
result表
user_id dpt_id dpt_name 1 1 产品 2 1 产品 3 2 技术 4 2 技术 5 3 其他部门
原数据表时user和dpt表，请使用hql实现result表中的结果.
select user.user_id,user.dpt_id,dpt.dpt_id from user join dpt on user.dpt_id = dpt.dpt_id;
41、需求如下千锋好程序大数据学院
t1_order表:
order_id order_type order_time 111 N 10:00 111 A 10:05 111 B 10:10 create table t1_order( order_id string, order_type string, order_time string ) row format delimited fields terminated by ' '; load data local inpath './hivedata/t1_order.txt' overwrite into table t1_order;
是用hql获取结果如下：
order_id order_type_1 order_type_2 order_time_1 order_time_2 111 N A 10:00 10:05 111 A B 10:05 10:10
select order_id,order_type_1,order_type_2,order_time_1,order_time_2 from( select order_id,order_type order_type_1, lead(order_type,1) over(sort by order_time) order_type_2, order_time order_time_1, lead(order_time,1) over(sort by order_time) order_time_2 from t1_order )A where order_type_2 is not null;
42、需求如下
t1_hobby表
name sex hobby janson 男 打乒乓球、游泳、看电影 tom 男 打乒乓球、看电影 drop table t1_hobby; create table t1_hobby( name string, sex string, hobby string ) row format delimited fields terminated by ' '; load data local inpath './hivedata/t1_hobby.txt' overwrite into table t1_hobby;
hobby最多3个值，使用hql实现结果如下:
name sex hobby1 hobby2 hobby3 janson 男 打乒乓球 游泳 看电影 tom 男 打乒乓球 看电影
select name,sex,split(hobby,"、")[0] hobby1, split(hobby,"、")[1] hobby2, nvl(split(hobby,"、")[2],"") hobby3 from t1_hobby;
