牛客-SQL-刷题（下）

上是一些基本操作回顾

按照牛客上的通过率，下应该难一点

31. 使用join查询方式找出没有分类的电影id以及名称

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

分析：film_id在film表中，但不在film_category表中

SELECT a.film_id, a.title FROM film a
LEFT JOIN film_category b
ON a.film_id=b.film_id
WHERE b.film_id is NULL;

32. 将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

分析：考察SQLite中字符串的拼接 ‘||’

SELECT last_name || ' ' || first_name name FROM employees;

33. 查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

分析：考察LEFT JOIN，左表有的记录都会展现出来，不论右表有没有

SELECT a.last_name, a.first_name, b.dept_no FROM employees a
LEFT JOIN dept_emp b
ON a.emp_no=b.emp_no;

34. 查找所有已经分配部门的员工的last_name和first_name以及dept_no

分析：主键emp_no在dept_emp中，以dept_emp作为左表

SELECT b.last_name, b.first_name, a.dept_no FROM dept_emp a
LEFT JOIN employees b
ON a.emp_no=b.emp_no;

35. 现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为'0000 00:00:00'

分析：知识盲区：设置默认值DEFAULT 在创建表时就应该注意到了

ALTER TABLE actor ADD create_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

36. 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ，没有分配具体的员工不显示

CREATE TABLE `dept_emp` ( `emp_no` int(11) NOT NULL,`dept_no` char(4) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `dept_manager` (`dept_no` char(4) NOT NULL,`emp_no` int(11) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` char(1) NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (`emp_no` int(11) NOT NULL,`salary` int(11) NOT NULL,`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY (`emp_no`,`from_date`));
CREATE TABLE `emp_bonus`(`emp_no` int not null,`recevied` datetime not null,`btype` smallint not null);

分析：btype, received选自emp_bonus表，dept_no选自dept_emp表，二者用emp_no连接起来

因为dep_emp表中的emp_no $subset$ employees表中的emp_no，所以不需要再连接筛选一遍

SELECT a.emp_no,a.dept_no, b.btype,b.recevied FROM dept_emp a
LEFT JOIN emp_bonus b
ON a.emp_no=b.emp_no;

注意：牛客后台emp_bonus中是recevied，不是recevied

37. 按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。

分析：整懵了，参考他人的题解：running_total给出的应该是前面所有员工的salary之和

把当前的emp_no和所有<=emp_no的连接起来，计算sum，根据当前的emp_no分类

注意是当前的salary，所以有to_date='9999-01-01'

SELECT a.emp_no, a.salary, sum(b.salary) running_total 
FROM salaries a, salaries b
WHERE b.emp_no<=a.emp_no AND a.to_date='9999-01-01' AND b.to_date='9999-01-01'
GROUP BY a.emp_no;

38. 对于employees表中，给出奇数行的first_name

分析：同上，用两个表连接，以b.first_name<=a.first_name的count(*)作为序列号

注意：这里比较的是字符串，不是b.emp_no<=a.emp_no

参考：SQL语句查询结果额外添加一列序号自动增加

https://www.cnblogs.com/tiantianne/archive/2009/10/13/1582368.html

SELECT first_name FROM employees a
WHERE(SELECT COUNT(*) FROM employees b
      WHERE b.first_name<=a.first_name)%2=1;

39. 统计出当前各个title类型对应的员工当前（to_date='9999-01-01'）薪水对应的平均工资。结果给出title以及平均工资avg。

分析：用两张表的emp_no连接，根据title分类

SELECT b.title, avg(a.salary) FROM salaries a,titles b
WHERE a.emp_no=b.emp_no AND a.to_date='9999-01-01'AND b.to_date='9999-01-01'
GROUP BY b.title;

40. 统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数sum

分析：将dept_emp表和salaries表用emp_no连接起来，找次数；从departments表中要dept_name

SELECT b.dept_no, c.dept_name, COUNT(*) FROM salaries a, dept_emp b, departments c
WHERE a.emp_no=b.emp_no AND b.dept_no=c.dept_no
GROUP BY b.dept_no;

41. 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no

分析：dept_emp表和dept_manager表的dept_no相同，这样才是同一个部门的；

经理的名字在dept_manager中，也在dept_emp中；员工的名字只在dept_emp中

SELECT a.emp_no, b.emp_no manager_no FROM dept_emp a, dept_manager b
WHERE a.dept_no=b.dept_no AND a.emp_no <> b.emp_no AND a.to_date='9999-01-01' AND b.to_date='9999-01-01';

42. 查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

分析：两次连用LFET JOIN

注意：不能用WHERE，因为要有没有分配部门的员工

SELECT a.last_name, a.first_name, c.dept_name FROM employees a
LEFT JOIN dept_emp b ON a.emp_no=b.emp_no
LEFT JOIN departments c ON b.dept_no=c.dept_no;

43. 获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

分析：两张表用emp_no连接；注意：两张表都需要to_date='9999-01-01'

SELECT a.dept_no, a.emp_no, b.salary FROM dept_manager a, salaries b
WHERE a.emp_no=b.emp_no AND a.to_date='9999-01-01' AND b.to_date='9999-01-01';

44. 获取有奖金的员工相关信息
给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。
bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%；当前薪水表示to_date='9999-01-01'

分析：先计算获得奖金的emp_no,对应的salary、bonus，用salaries表和emp_bonus表
再找出emp_no对应的first_name, last_name

知识盲区：CASE 列名
WHEN 列值1 THEN ...
WHEN 列值2 THEN ...
ELSE ... END

或者是

CASE
  WHEN 列名=条件1 THEN ...
  WHEN 列名=条件2 THEN ...
  ELSE ... END

SELECT e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
(CASE b.btype 
WHEN 1 THEN s.salary*0.1
WHEN 2 THEN s.salary*0.2
ELSE s.salary*0.3 END) bonus
FROM employees e, emp_bonus b, salaries s
WHERE b.emp_no=e.emp_no AND s.emp_no=b.emp_no AND s.to_date='9999-01-01';

45. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列

提示：在sqlite中获取datetime时间对应的年份函数为strftime('%Y', to_date)

欲望才是原动力