面试（1）SQL

最新推荐文章于 2022-05-07 22:53:21 发布

僕の名前を

最新推荐文章于 2022-05-07 22:53:21 发布

阅读量320

点赞数 1

文章标签： sql hive

本文链接：https://blog.csdn.net/weixin_43915186/article/details/106331219

版权

有两张表及其测试数据，如下

部门表dep
dept_id dept_name
部门编号部门名称
D1 部门1
D2 部门2
D3 部门3
D4 部门4
D5 部门5

员工表emp
emp_id emp_lname emp_fname birthday gender dept_id emp_wage
员工编号员工姓员工名出生日期性别部门编号工资
E1 张三 1995-03-10 男 D1 2200
E2 李四 1987-01-11 男 D1 3000
E3 王五 1991-04-09 女 D2 2700
E4 姓名1 1985-05-01 男 D2 3500
E5 姓名2 1996-06-07 男 D3 1900
E6 姓名3 1993-03-22 女 D3 2500
E7 姓名4 1991-02-23 男 D3 2900
E8 姓名5 1991-07-13 男 D4 2600
E9 姓名6 1990-12-19 女 D4 2900
E10 姓名7 1984-11-09 女 D4 3500
E11 姓名8 1994-10-07 男 D5 2300
E12 姓名9 1990-12-02 女 D5 2800
E13 姓名10 1988-03-19 男 D5 3000
E14 姓名11 1983-11-18 男 D5 3800

按要求写出如下SQL：
1、公司所有员工中工资最高的3位员工的员工id、部门名称、员工工资（不考虑工资相同的情况）；
2、列出工资大于等于3000的员工所属的部门名、员工id、员工姓名和员工工资；
3、列出部门中员工人数大于等于3人的部门编号、部门名称；
4、求各部门的平均工资，输出部门id、部门名称、平均工资；
5、用一条SQL语句查出部门每个员工的工资都大于等于2500的部门名称；
6、公司所有员工中年龄在20-25岁的员工的最高工资、最低工资、平均工资；
7、建表语句（不用生成数据）
a) 表1：dept_id为主键，dept_id、dept_name都是字符型。
b) 表2：emp_id为主键，emp_id、emp_lname、deemp_fname、gender、dept_id为字符型，birthday为日期型，emp_wage为数值型(小数位2位)；
8、查询出所有员工的姓名、部门名称、工资、工资等级。其中工资等级划分：0-2000为第一级，2001-3000为第二级，大于等于3001为第三级；

问题总结：

第一题排序，desc没有设置（row_number、rank、dense_rank没有彻底弄清楚）
Concat_ws()和concat()没有使用正确，在表中姓名是由两个字段组成，concat(emp_lname),concat_ws(emp_lname,””,emp_rname)
根据出生日期算年龄：首先要知道当前日期（current_date）,然后判断月日和出生月日作比较

第一题
select  emp_id,emp_name,dept_name,emp_wage
from(select  emp_id,emp_name,dept_id,emp_wage
from(select emp_id,concat(emp_lname,emp_fname)  emp_name,dept_id,emp_wage,row_number() over(order by emp_wage) rk
from emp)emp_temp01 where rh<4)emp_temp02 join dep on dep.dept_id=emp_temp02.dept_id;
第二题
select dep.dept_name,emp_id,emp_name,emp_wage
from(select dept_id,emp_id,concat(emp_lname,emp_fname) emp_name,emp_wage
from emp  where emp_wage>=3000)emp_temp03 join dep on emp_temp03.dept_id=dep.dept_id;
第三题
select  dep.dept_id,dep.dept_name
from(select emp.emp_id
from emp group by emp.dept_id  having count(1)>=3)temp01  join dep on emp.emp_id=dep.dept_id;
第四题

select dep.dept_id,dep.dept_name,avg
from(select emp.dept_id,avg(emp_wage) avg
from emp  group by emp.emp_wage)temp02 join dept on emp.emp_id=dep.dept_id;
第五题

select dep.dept_id,dep,dept_name
from(select emp.dept_id
from emp group by emp.dept_id having min(emp.emp_wage)>=2500)temp03
join dept on emp.emp_id=dep.dept_id;


第六题
select emp.emp_id,max(emp_wage) max,min(emp_wage) min,avg(emp_wage) avg
from(select emp.emp_id,emp.emp_wage
from(select if(datediff(CURRENT_DATE,CONCAT(substr(CURRENT_DATE,0,4),substr(birthday,5,7)))>=0,
	(substr(CURRENT_DATE,0,4) - substr(birthday,0,4)),
	(substr(CURRENT_DATE,0,4) - substr(birthday,0,4)-1)) as age,emp.emp_id,emp.emp_wage
from emp)temp04 where age>=20 and age<=25)temp05 group by emp.emp_id;


select
(case when  year(CURRENT_DATE)>year(birthday) and  month(CURRENT_DATE)>month(CURRENT_DATE) then year(CURRENT_DATE)-year(birthday)
	  when	year(CURRENT_DATE)>year(birthday) and month(CURRENT_DATE)=month(CURRENT_DATE) and day(CURRENT_DATE)>=day(birthday) then  year(CURRENT_DATE)-year(birthday)
	  when	year(CURRENT_DATE)>year(birthday) and month(CURRENT_DATE)=month(CURRENT_DATE) and day(CURRENT_DATE)<day(birthday) then  year(CURRENT_DATE)-year(birthday)+1
	  when year(CURRENT_DATE)>year(birthday) and month(CURRENT_DATE)<month then year(CURRENT_DATE)-year(birthday)+1
	) as age
from emp


第八题
select 
concat(emp_lname,emp_fname) emp_name,
dep.dept_name,
emp.emp_wage,
(case when emp_wage>=0 and emp_wage<=2000 then 1
	when emp_wage>2000 and emp<=3000 then 2
	when emp_wage>3000 then 3
) as level
from emp join dep on emp.dept_id=dep.dept_id;

//sparkSQL
import org.apache.spark.sql.{DataFrame, SparkSession}

object Test {
  def main(args: Array[String]): Unit = {
    val spark=SparkSession.builder()
      .appName("test")
      .master("local[*]")
      .getOrCreate()
    /**定义输出日志级别*/
    spark.sparkContext.setLogLevel("WARN")
    /**读取dep表数据*/
    val depDF: DataFrame = spark.read.format("csv")
      .option("header", "true")
      .option("inferSchema", "true") //是否自动推到内容的类型
      .option("delimiter", " ")
      .load("F:\\restudy\\data\\dep.csv")
   /* depDF.show()*/
    /**读取emp表数据*/
    val empDF: DataFrame = spark.read.format("csv")
      .option("header", "true")
      .option("inferSchema", "true")
      .option("delimiter", " ")
      .load("data/emp.csv")

    import spark.sql._
    /**创建临时视图*/
    depDF.createTempView("dep")
    empDF.createTempView("emp")
    //empDF.show()
    /**第一题*/
//    spark.sql(
//      """
//        |select  emp_id,emp_name,dept_name,emp_wage
//        |from(select  emp_id,emp_name,dept_id,emp_wage
//        |from(select emp_id,concat_ws(emp_lname,emp_fname)  emp_name,dept_id,emp_wage,row_number() over(order by emp_wage desc) rk
//        |from emp)emp_temp01 where rk<4)emp_temp02 join dep on dep.dept_id=emp_temp02.dept_id
//        |""".stripMargin).show()
    /**第二题*/
    spark.sql(
      """
        |select dep.dept_name,emp_id,emp_name,emp_wage
        |from(select dept_id,emp_id,concat(emp_lname,emp_fname) emp_name,emp_wage
        |from emp  where emp_wage>=3000)emp_temp03 join dep on emp_temp03.dept_id=dep.dept_id
        |""".stripMargin).show()
 
    /**程序关闭*/
      spark.stop()
  }
}