HQL面试题(一)
1. 数据准备
- gz.txt 用户工资组成表
用户id(uid), 基本工资(jb) 奖金(jj) 提成(tc) 部门编号( deptno)
1,2000,3000,1500,1
2,5000,500,1000,2
3,1500,1000,3000,2
4,3000,6000,8000,3
5,1500,2000,1800,1
6,2500,1000,1900,1
- bm.txt 部门表
1,销售
2,技术
3,行政
- yg.txt 员工信息表
uid name gender age
1,zs,M,28
2,ww,F,36
3,zl,F,48
4,pp,M,44
5,wb,M,32
6,TQ,F,32
2. 建表并到导入数据到Hive数据表中
- 建立数据库db_company
create database db_company;
use db_company;
- 建表
# 在db_company 创建工资表
create table tb_gz(
uid int,
jb int,
jj int,
tc int,
deptno int
)
row format delimited fields terminated by ",";
# 将linux节点服务器上本地文件加载到hive数据表中
load data local inpath "/root/hive_sql/gz.txt" into table gz;
# 建立部门表
create table bm(
deptno string ,
name string
)
row format delimited fields terminated by ",";
# 将本地文件导入数据表中
load data local inpath "/root/hive_sql/bm.txt" into table bm;
create table yg(
uid int,
name string,
gender string,
age int
)
row format delimited fields terminated by ",";
load data local inpath "/root/hive_sql/yg.txt" into table yg;
- 表格建立后,数据如下
+---------+--------+--------+--------+------------+
| gz.uid | gz.jb | gz.jj | gz.tc | gz.deptno |
+---------+--------+--------+--------+------------+
| 1 | 2000 | 3000 | 1500 | 1 |
| 2 | 5000 | 500 | 1000 | 2 |
| 3 | 1500 | 1000 | 3000 | 2 |
| 4 | 3000 | 6000 | 8000 | 3 |
| 5 | 1500 | 2000 | 1800 | 1 |
| 6 | 2500 | 1000 | 1900 | 1 |
+---------+--------+--------+--------+------------+
+------------+----------+
| bm.deptno | bm.name |
+------------+----------+
| 1 | 销售 |
| 2 | 技术 |
| 3 | 行政 |
+------------+----------+
+---------+----------+------------+---------+
| yg.uid | yg.name | yg.gender | yg.age |
+---------+----------+------------+---------+
| 1 | zs | M | 28 |
| 2 | ww | F | 36 |
| 3 | zl | F | 48 |
| 4 | pp | M | 44 |
| 5 | wb | M | 32 |
| 6 | TQ | F | 32 |
+---------+----------+------------+---------+
3. 题目
3.1 求出公司中每个员工的姓名 和 三类收入中最高的那种收入的类型
- 思路,
- 需要员工名字,收入,不涉及部门,所以yg表和gz表join
- 收入最高,也就是需要求出三个收入部分最高的,查阅资料可以知道有一个函数greatest
- 需要知道哪个部分最高,使用case when
- sql
- 第一步,求出名字以及每个人对应工资中最高的部分信息
select
yg.name,
greatest(gz.jb, gz.jj, gz.tc) as highest
from
gz
join
yg
on
gz.uid=yg.uid;
+----------+----------+
| yg.name | highest |
+----------+----------+
| zs | 3000 |
| ww | 5000 |
| zl | 3000 |
| pp | 8000 |
| wb | 2000 |
| TQ | 2500 |
+----------+----------+