牛客网SQL刷题1-10

牛客网SQL刷题：

1. 查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)

建表：
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`));

答案：
select * from employees where hire_date = 
(select max(hire_date)from employees);

2、查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天

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`));
答案：
select * from empolyees where hire_date=(select distinct hire_date from empolyees order  by hire_date  desc limit 1 offset 2);

limit的用法：
以下的两种方式均表示取2,3,4三条条数据。

1.select* from test LIMIT 1,3;
当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量。
2.select * from test LIMIT 3 OFFSET 1;(在mysql 5以后支持这种写法)
当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

解法：
1.使用limit语句搭配offset使用，可以检索到入职日期第三晚的员工信息，前提（目前所有的数据里员工入职的日期都不是同一天）。

select *
from employees
order by hire_date desc
limit 1 offset 2;

2.使用子查询可以检索多个入职日期相同的员工:
(1).使用解法1加上distinct语句可以查询到入职期第三晚的日期是多少。
(2).通过使用where语句，使用子查询可以检索出多个入职日期为第三晚的员工。

select *
from employees
where hire_date = (
select distinct hire_date
from employees
order by hire_date desc
limit 1 offset 2);

3.查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no(注:输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_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 `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

解法1：纯where 表连接
select s.*,d.dept_no from salaries as s,dept_manager as d
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
and s.emp_no=d.emp_no;

解法2：使用内连接
select s.*,d.dept_no from salaries as s inner join dept_manager as d 
on d.emp_no=s.emp_no
where d.to_date='9999-01-01'
and s.to_date='9999-01-01'
order by emp_no;

解法3：使用左连接
select s.*,d.dept_no from salaries as s left join dept_manager as d 
on d.emp_no=s.emp_no 
where s.to_date='9999-01-01'
and d.to_date='9999-01-01'
order by s.emp_no asc

4.查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序).

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 `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`));

方法一
select e.last_name,e.first_name,d.dept_no
from dept_emp d inner join employees e on d.emp_no=e.emp_no
方法二
select e.last_name,e.first_name,d.dept_no
from dept_emp d left join employees e on d.emp_no=e.emp_no
方法三
select e.last_name,e.first_name,d.dept_no
from dept_emp d,employees e
where d.emp_no=e.emp_no

5.查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)

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 `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`));

使用左连接：
select last_name,first_name,dept_no 
from employees e left join dept_emp d
on e.emp_no=d.emp_no;

注意：
INNER JOIN 两边表同时有对应的数据，即任何一边缺失数据就不显示。
LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
RIGHT JOIN 会读取右边数据表的全部数据，即便左边表无对应数据。

来源：牛客网
注意on与where有什么区别，两个表连接时用on，在使用left jion时，on和where条件的区别如下：
1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

6、所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_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`));

方法1：子查询,先查出hire_date，然后查询要找的内容，接着对所有查找内容进行排序。
select s.emp_no, s.salary
from salaries as s
where from_date = (select e.hire_date from employees as e where e.emp_no = s.emp_no)
order by s.emp_no desc;

方法2：使用内连接，hire_date时间和from_date时间一致，
select e.emp_no, s.salary
from employees as e
inner join salaries as s
on e.emp_no = s.emp_no
where s.from_date = e.hire_date
order by e.emp_no desc;

方法3：使用(逗号)并列查询，hire_date时间和from_date时间一致
select e.emp_no, s.salary
from employees as e, salaries as s
where e.emp_no = s.emp_no
and s.from_date = e.hire_date
order by e.emp_no desc;
注意：内连接是取左右两张表的交集形成一个新表，用From并列两张表后仍然是两张表。如果还要对新表进行操作则要用内连接。从效率上看应该并列查询更快，因为不用形成新表。

7、查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

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`));

select emp_no, count(emp_no) as t from salaries group by emp_no having t > 15;

此题应注意以下四点：
1、用COUNT()函数和GROUP BY语句可以统计同一emp_no值的记录条数
2、根据题意，输出的涨幅次数为t，故用AS语句将COUNT(emp_no)的值转换为t
3、由于COUNT()函数不可用于WHERE语句中，故使用HAVING语句来限定t>15的条件
4、最后存在一个理解误区，涨幅超过15次，salaries中相应的记录数应该超过16（从第2条记录开始算作第1次涨幅），不过题目为了简单起见，将第1条记录当作第1次涨幅，所以令t>15即可
/** 注意： 严格来说，下一条salary高于本条才算涨幅，但本题只要出现了一条记录就算一次涨幅，salary相同可以理解为涨幅为0，salary变少理解为涨幅为负 **/
还有就是group by与order by有什么区别，order by就是排序。而group by就是分组，举个例子好说点，group by 单位名称,这样的运行结果就是以“单位名称”为分类标志统计各单位的职工人数和工资总额。
这样可以更好的分下类，更好看一些。还有就是为什么没有用where而是用的having，记住下面的两句话就好了。
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。

8、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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`));

方法1：distinct+order by
select distinct salary
from salaries
where to_date='9999-01-01'
order by salary desc;

方法2：distinct+order by
select salary
from salaries
where to_date='9999-01-01'
group by salary
order by salary desc;

补充：
WHERE语句在GROUP BY语句之前，SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后，SQL会在分组之后计算HAVING语句。
说明：
对于distinct与group by的使用：
1.当对系统的性能高并且数据量大时使用group by
2.当对系统的性能不高时或者使用数据量少时两者皆可
3.尽量使用group by

9、 获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary，输出结果按照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 `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`));

1、使用内连接：
select d.dept_no,d.emp_no,s.salary 
from dept_manager as d inner join salaries as s
on d.emp_no=s.emp_no
and d.to_date=s.to_date
and d.to_date="9999-01-01"
order by dept_no;

2、使用子查询：
select d.dept_no,d.emp_no,s.salary 
from dept_manager as d ,salaries as s
where d.emp_no=s.emp_no
and d.to_date="9999-01-01"
and s.to_date="9999-01-01"
order by d.dept_no;

10、获取所有非manager的员工emp_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`));

解法一：使用子查询
select emp_no from employees where emp_no 
not in(select emp_no from dept_manager);

解法二：使用左连接
select e.emp_no from employees as e left join dept_manager as d 
on e.emp_no=d.emp_no
where dept_no is null;