SQL

1.查找最晚入职员工的所有信息

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

（2）解法：
方法一：子查询-先找出 hire_date 字段的最大值，再把该值当成 employees 表的 hire_date 查询条件。

SELECT * FROM employees WHERE hire_date = (SELECT MAX(hire_date) FROM employees);

方法二：排序-对hire_date字段排序降序，此时最晚的时间排在第一个，再用LIMIT取出。

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 0,1;

2.查找入职员工时间排名倒数第三的员工所有信息

（1）题目描述：查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天。
（2）解法：（LIMIT m,n : 表示从第m+1条开始，取n条数据；LIMIT n ： 表示从第0条开始，取n条数据，是limit(0,n)的缩写。）
方法一：子查询

select * from employees 
where hire_date = 
(select hire_date from employees group by hire_date order by hire_date desc limit 2,1)；

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

排序
select * from employees order by hire_date desc limit 2,1;

3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

（1）题目描述：查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no。
(注:请以salaries表为主表进行查询，输出结果以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`)
);

（2）解法：

select s.*,d.dept_no
from salaries s
join dept_manager d
on s.emp_no = d.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
order by s.emp_no; 

4.查找所有已经分配部门的员工的last_name和first_name

（1）题目描述：查找所有已经分配部门的员工的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`)
);

（2）解法：

select last_name,first_name,d.dept_no
from employees e
join dept_emp d
on e.emp_no = d.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no

（1）题目描述：查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括暂时没有分配具体部门的员工(请注意输出描述里各个列的前后顺序)，表同题4。
（2）解法：

select last_name,first_name,d.dept_no
from employees e
left join dept_emp d
on e.emp_no = d.emp_no;

6.查找所有员工入职时候的薪水情况

（1）题目描述：查找所有员工入职时候的薪水情况，给出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`)
);

（2）解法：

方法1：
select s.emp_no,s.salary
from salaries s
join employees e
on s.emp_no = e.emp_no and s.from_date = e.hire_date
order by s.emp_no desc;

方法2：
select emp_no,salary 
from salaries
group by emp_no 
having min(from_date)
order by emp_no DESC;

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

（1）题目描述：查找薪水变动超过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`)
);

（2）解法：
方法1：

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

方法2：1.下一条salary高于本条才算涨幅，大部分答案都是只要出现了一条记录就算一次涨幅。2.个人比较严格的话，salary高于前一条才算涨幅，对于每条记录查询它上一条记录判断生成临时表，在做统计就好了。

SELECT tmp.emp_no,
       Count(c) AS t
FROM   (SELECT emp_no,
               IFNULL(s1.salary > (SELECT s2.salary
                            FROM   salaries AS s2
                            WHERE  s1.from_date > s2.from_date
                            and s1.emp_no  = s2.emp_no
                            ORDER  BY s2.from_date DESC
                            LIMIT  1),1) AS c,
               s1.from_date
        FROM   salaries AS s1) tmp
GROUP  BY tmp.emp_no
HAVING Count(*) > 15;

8.找出所有员工当前具体的薪水salary情况

（1）题目描述：找出所有员工当前(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`)
);

（2）解法：对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by。 2、当对系统的性能不高时使用数据量少时两者皆可。 3、尽量使用group by。

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

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

9.获取所有部门当前manager的当前薪水情况

（1）题目描述：获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)

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

（2）解法：

select d.dept_no, d.emp_no,s.salary
from dept_manager d
join salaries s
on d.emp_no = s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01';

10.获取所有非manager的员工emp_no

（1）题目描述：获取所有非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`)
);

（2）解法：

方法1：
select e.emp_no
from employees e
left join dept_manager d 
on e.emp_no = d.emp_no
where d.emp_no is null;

方法2：使用NOT IN选出在employees但不在dept_manager中的emp_no记录。
SELECT emp_no FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)

11.获取所有员工当前的manager

（1）题目描述：获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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 `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`)
);

（2）解法：

select e.emp_no,d.emp_no manager_no
from dept_emp e 
left join dept_manager d 
on e.dept_no = d.dept_no 
where e.emp_no <> d.emp_no and d.to_date='9999-01-01';

12.获取所有部门中当前员工薪水最高的相关信息

（1）题目描述：获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。

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

（2）解法：
方法1：如果同部门有多条同等最大salary，仅显示一条

select r.dept_no,r.emp_no,max(r.salary) 
from 
(
select d.dept_no,d.emp_no,s.salary 
from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
order by s.salary desc
) as r
group by r.dept_no
order by r.dept_no asc;

方法2：如果同部门有多条同等最大salary，一起显示出来

select r.dept_no,ss.emp_no,r.maxSalary
from (
select d.dept_no,max(s.salary) as maxSalary 
from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc;

方法3：因为 GROUP BY e.dept，背后实现的过程是先按 e.dept 排序再进行分组，最后输出的是按 e.dept 做升序排序后的结果；不过，最好带上 ORDER BY，不能仅依赖 GROUP BY 的排序，反正带上一定不会错；

select d.dept_no,d.emp_no,max(s.salary)
from dept_emp d
join salaries s 
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01' and s.to_date = '9999-01-01'
group by d.dept_no
order by d.dept_no;

13.从titles表获取按照title进行分组

（1）题目描述：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。

CREATE TABLE IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL
);

（2）解法：

select title,count(*) t 
from titles
group by title
having t>=2;

14.从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略

（1）题目描述：从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，title对应的数目t不增加)。

CREATE TABLE IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL
);

（2）解法：

select title,count(distinct emp_no) t 
from titles
group by title
having t>=2;

15.查找employees表所有emp_no为奇数

（1）题目描述：查找employees表所有emp_no为奇数，且last_name不为Mary(注意大小写)的员工信息，并按照hire_date逆序排列(题目不能使用mod函数)

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

（2）解法：

select *
from employees
where emp_no%2=1 and last_name <> 'Mary'
order by hire_date desc;
——————————————————————————————
select *
from employees e
where e.emp_no & 1 = 1 and e.last_name != 'Mary'
ORDER BY e.hire_date desc

16.统计出当前各个title类型对应的员工当前薪水对应的平均工资

（1）题目描述：统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。

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 IF NOT EXISTS "titles" (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL
);

（2）解法：

select t.title,avg(s.salary) avg
from titles t 
join salaries s 
on t.emp_no = s.emp_no
where t.to_date='9999-01-01' and s.to_date='9999-01-01'
group by t.title;

17.获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水salary

（1）题目描述：获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水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`)
);

（2）解法：

select emp_no,salary
from salaries
where salary =(
select distinct salary 
from salaries
where to_date = '9999-01-01'
order by salary desc
limit 1,1
)
and to_date = '9999-01-01';
——————————————————
select emp_no, salary
from salaries
where salary = (
    select salary from salaries 
    group by salary
    order by salary desc limit 1,1
) 
and to_date = '9999-01-01'

18.查找当前薪水排名第二多的员工编号emp_no

（1）题目描述：查找当前薪水(to_date=‘9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，你可以不使用order by完成吗.

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

（2）解法：

解法1：max 任意相同工资人数
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e
join salaries s 
on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = (
     select max(salary)
                from salaries
                where salary<(select max(salary) 
                              from salaries 
                              where to_date='9999-01-01'
                             )
                        and to_date='9999-01-01'
                );

解法2：第二种 通用型可以求任意第几高，并且可以求多个形同工资
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e
join salaries s 
on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary = 
(
     select s1.salary
     from     salaries s1
     join salaries s2
     on s1.salary<=s2.salary 
     and s1.to_date='9999-01-01' and s2.to_date='9999-01-01'
     group by s1.salary
     having count(distinct s2.salary)=2
 )

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

（1）题目描述：查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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`)
);

（2）解法：

select e.last_name,e.first_name,dp.dept_name
from employees e 
left join dept_emp d on e.emp_no = d.emp_no
left join departments dp on d.dept_no = dp.dept_no;

20.查找员工编号emp_now为10001其自入职以来的薪水salary涨幅值growth

（1）题目描述：查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪，没有降薪)

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

（2）解法：

select max(salary)-min(salary) growth 
from salaries
where emp_no = 10001;

SELECT ( 
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth;

21.查找所有员工自入职以来的薪水涨幅情况

（1）题目描述：查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序。（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!=‘9999-01-01’，这样的数据不显示在查找结果里面）

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

（2）解法：

select e.emp_no,(max(s.salary)-min(s.salary)) growth
from employees e 
inner join salaries s 
on e.emp_no = s.emp_no
where s.to_date = '9999-01-01'
group by emp_no
order by growth;（这种方法不可以，可能有降工资）
————————————————————————————————————
select a.emp_no, (b.salary - c.salary) as growth
from employees as a
inner join salaries as b
on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
inner join salaries as c
on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc;

22.统计各个部门的工资记录数

（1）题目描述：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 `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`)
);

（2）解法：

select dp.dept_no,dp.dept_name,count(s.salary) sum
from departments dp
left join dept_emp d on d.dept_no = dp.dept_no
left join salaries s on s.emp_no = d.emp_no
group by dp.dept_no;

23.对所有员工的当前薪水按照salary进行按照1-N的排名

（1）题目描述：对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1-N的排名，相同salary并列且按照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`)
);

（2）解法：

select s1.emp_no,s1.salary,count(distinct s2.salary) rank
from salaries s1
join salaries s2
on s1.salary <= s2.salary
where s1.to_date = '9999-01-01'  AND s2.to_date = '9999-01-01'
group by s1.emp_no
order by s1.salary DESC, s1.emp_no ASC；

24.获取所有非manager员工当前的薪水情况

（1）题目描述：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=‘9999-01-01’

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

（2）解法：

select de.dept_no,de.emp_no,s.salary
from salaries s 
join dept_emp de on s.emp_no = de.emp_no 
    and s.to_date = '9999-01-01'
    and de.to_date = '9999-01-01'
join dept_manager dm on de.dept_no = dm.dept_no
where de.emp_no <> dm.emp_no;

25.获取员工其当前的薪水比其manager当前薪水还高的相关信息

（1）题目描述：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=‘9999-01-01’, 结果第一列给出员工的emp_no，第二列给出其manager的manager_no，第三列给出该员工当前的薪水emp_salary,第四列给该员工对应的manager当前的薪水manager_salary

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

（2）解法：分解为三部分。1、显示员工以及其Manager。2、显示员工的薪水以及Manager的薪水。3、筛选员工薪酬比Manager高的部分人员，包括经理自己。

SELECT dpe.emp_no,dp.emp_no AS manager_no,
		sa1.salary AS emp_salary,
		sa2.salary AS manager_salary  
FROM dept_emp dpe
LEFT JOIN dept_manager dp ON dpe.dept_no=dp.dept_no
LEFT JOIN salaries sa1 ON sa1.emp_no = dpe.emp_no
LEFT JOIN salaries sa2 ON sa2.emp_no = dp.emp_no
WHERE dpe.to_date='9999-01-01' 
AND dp.to_date='9999-01-01'
AND sa1.to_date='9999-01-01' 
AND sa2.to_date='9999-01-01'
AND sa1.salary > sa2.salary;
————————————————————————
select t1.emp_no,manager_no,emp_salary,manager_salary
from
    (
    select dept_no,d1.emp_no,salary as emp_salary
    from dept_emp as d1 
    inner join salaries as s 
    on d1.emp_no=s.emp_no
    where d1.to_date='9999-01-01' and s.to_date='9999-01-01'
    ) t1 ---员工工资表
inner join
    (
    select dept_no,d2.emp_no as manager_no,salary as manager_salary
    from dept_manager as d2 
    inner join salaries as s 
    on d2.emp_no=s.emp_no
    where d2.to_date='9999-01-01' and s.to_date='9999-01-01'
    ) t2 ---部门经理工资表
on t1.dept_no=t2.dept_no  -----员工工资表与经理工资表是根据同个部门联结的
where t1.emp_salary>t2.manager_salary;
——————————————————————————
select de.emp_no,f.manager_no,s1.salary emp_salary,f.manager_salary
from dept_emp de
join  salaries s1
on s1.emp_no = de.emp_no
join 
    (select dept_no,d.emp_no manager_no,s.salary manager_salary
    from dept_manager d 
    join salaries s 
    on d.emp_no = s.emp_no
    where d.to_date = '9999-01-01' and s.to_date = '9999-01-01'
    ) as f
    on de.dept_no = f.dept_no
where de.to_date = '9999-01-01'
    and s1.to_date = '9999-01-01' 
    and s1.salary > f.manager_salary;

26.汇总各个部门当前员工的title类型的分配数目

（1）题目描述：汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的当前(dept_emp.to_date = ‘9999-01-01’)员工的当前(titles.to_date = ‘9999-01-01’)title以及该类型title对应的数目count。(注：因为员工可能有离职，所有dept_emp里面to_date不为’9999-01-01’就已经离职了，不计入统计，而且员工可能有晋升，所以如果titles.to_date 不为 ‘9999-01-01’，那么这个可能是员工之前的职位信息，也不计入统计)。

CREATE TABLE `departments` (
`dept_no` char(4) NOT NULL,
`dept_name` varchar(40) NOT NULL,
PRIMARY KEY (`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 IF NOT EXISTS `titles` (
`emp_no` int(11) NOT NULL,
`title` varchar(50) NOT NULL,
`from_date` date NOT NULL,
`to_date` date DEFAULT NULL
);

（2）解法：

select e.dept_no, e.dept_name, t.title,count(*) count
from (select d.dept_no, d.dept_name, de.emp_no
from departments d 
join dept_emp de 
on d.dept_no = de.dept_no
where de.to_date = '9999-01-01') e
join titles t 
on e.emp_no = t.emp_no
where t.to_date = '9999-01-01'
group by e.dept_no,t.title;

--------------------------------------------------------------------------------
select d.dept_no,d.dept_name,t.title,count(*)
from dept_emp de
inner join departments d
on de.dept_no = d.dept_no 
inner join titles t
on de.emp_no=t.emp_no
where de.to_date = '9999-01-01' and t.to_date = '9999-01-01'
group by d.dept_no,t.title;

27. 给出每个员工每年薪水涨幅超过5000的员工编号emp_no

（1）题目：给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。 提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date) 。(数据保证每个员工的每条薪水记录to_date-from_date=1年，而且同一员工的下一条薪水记录from_data=上一条薪水记录的to_data)

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

（2）解法：

select s1.emp_no,s2.from_date,s2.salary-s1.salary salary_growth
from salaries s1 
join salaries s2
on s1.emp_no = s2.emp_no and s1.to_date = s2.from_date
where s2.salary-s1.salary >5000
order by salary_growth desc;

--------------------------------------------------------------------------------
select s1.emp_no,s2.from_date,s2.salary-s1.salary salary_growth
from salaries s1 
join salaries s2
on s1.emp_no = s2.emp_no and (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 )
where s2.salary-s1.salary >5000
order by salary_growth desc;

28.查找描述信息中包括robot的电影对应的分类名称以及电影数目

（1）题目：

CREATE TABLE IF NOT EXISTS film (
film_id smallint(5)  NOT NULL DEFAULT '0',
title varchar(255) NOT NULL,
description text,
PRIMARY KEY (film_id)
);

CREATE TABLE category  (
category_id  tinyint(3)  NOT NULL ,
name  varchar(25) NOT NULL, `last_update` timestamp,
PRIMARY KEY ( category_id )
);

CREATE TABLE film_category  (
film_id  smallint(5)  NOT NULL,
category_id  tinyint(3)  NOT NULL, `last_update` timestamp
);

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

如：输入为:
INSERT INTO film VALUES(1,'ACADEMY DINOSAUR','A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies');
INSERT INTO film VALUES(2,'ACE GOLDFINGER','A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China');
INSERT INTO film VALUES(3,'ADAPTATION HOLES','A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory');
INSERT INTO film VALUES(4,'AFFAIR PREJUDICE','A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank');
INSERT INTO film VALUES(5,'AFRICAN EGG','A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico');
INSERT INTO film VALUES(6,'AGENT TRUMAN','A Intrepid Panorama of a robot And a Boy who must Escape a Sumo Wrestler in Ancient China');
INSERT INTO film VALUES(7,'AIRPLANE SIERRA','A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat');
INSERT INTO film VALUES(8,'AIRPORT POLLOCK','A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India');
INSERT INTO film VALUES(9,'ALABAMA DEVIL','A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat');
INSERT INTO film VALUES(10,'ALADDIN CALENDAR','A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China');

INSERT INTO category VALUES(1,'Action','2006-02-14 20:46:27');
INSERT INTO category VALUES(2,'Animation','2006-02-14 20:46:27');
INSERT INTO category VALUES(3,'Children','2006-02-14 20:46:27');
INSERT INTO category VALUES(4,'Classics','2006-02-14 20:46:27');
INSERT INTO category VALUES(5,'Comedy','2006-02-14 20:46:27');
INSERT INTO category VALUES(6,'Documentary','2006-02-14 20:46:27');
INSERT INTO category VALUES(7,'Drama','2006-02-14 20:46:27');
INSERT INTO category VALUES(8,'Family','2006-02-14 20:46:27');
INSERT INTO category VALUES(9,'Foreign','2006-02-14 20:46:27');
INSERT INTO category VALUES(10,'Games','2006-02-14 20:46:27');
INSERT INTO category VALUES(11,'Horror','2006-02-14 20:46:27');
INSERT INTO category VALUES(12,'Music','2006-02-14 20:46:27');
INSERT INTO category VALUES(13,'New','2006-02-14 20:46:27');
INSERT INTO category VALUES(14,'Sci-Fi','2006-02-14 20:46:27');
INSERT INTO category VALUES(15,'Sports','2006-02-14 20:46:27');
INSERT INTO category VALUES(16,'Travel','2006-02-14 20:46:27');

INSERT INTO film_category VALUES(1,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(2,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(3,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(4,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(5,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(6,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(7,5,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(8,6,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(9,11,'2006-02-14 21:07:09');
INSERT INTO film_category VALUES(10,15,'2006-02-14 21:07:09');

输出为:
分类名称category.name 电影数目count(film.film_id)
Documentary 1

（2）解法：

SELECT c.name, count(c.name)
FROM category as c 
inner join film as f 
ON c.category_id = fc.category_id
inner join film_category as fc 
on f.film_id = fc.film_id 
WHERE fc.category_id = 
(SELECT category_id 
 FROM film_category 
 GROUP BY category_id 
 HAVING count(film_id)>=5
)
AND f.description LIKE '%robot%'
GROUP BY c.name;

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

（1）题目：使用join查询方式找出没有分类的电影id以及名称。三个表，表与表28相同，输出如下。

（2）解法：

select f.film_id,f.title
from film f
left join film_category fc
on f.film_id = fc.film_id
where fc.category_id is null;

30.使用子查询的方式找出属于Action分类的所有电影对应的title,description

（1）题目：你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗 。三个表，表与表28相同，输出如下。

（2）解法：

select f.title,f.description
from film f 
join film_category fc
on f.film_id = fc.film_id
join category c 
on fc.category_id = c.category_id
where c.name = 'Action';

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

（1）题目：将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分。(注：该数据库系统是sqllite,字符串拼接为 || 符号，不支持concat函数)。

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

（2）解法：不同数据库连接字符串的方法不完全相同，MySQL、SQL Server、Oracle等数据库支持CONCAT方法，而本题所用的SQLite数据库只支持用连接符号"||"来连接字符串。

select concat(last_name,' ',first_name) Name
from employees;
--------------------------------------------------------------------------------
select (last_name || " " || first_name) as Name 
from employees;

33.创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))

（1）题目：创建一个actor表，包含如下列信息(注：sqlite获取系统默认时间是datetime(‘now’,‘localtime’))
（2）解法：

create table if not exists actor(
    actor_id smallint(5) not null,
    first_name varchar(45) not null,
    last_name varchar(45) not null,
    last_update timestamp not null default(datetime('now','localtime')),
    primary key(actor_id)
)
--------------------------------------------------------------------------------
create table if not exists actor(
    actor_id smallint(5)  not null ,
    first_name varchar(45)  not null,
    last_name varchar(45)  not null,
    last_update timestamp  not null default (datetime('now','localtime')),
    primary key(actor_id)
)

34.对于表actor批量插入如下数据(不能有2条insert语句哦!)

（1）题目：对于表actor批量插入如下数据(不能有2条insert语句哦!)

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

（2）解法：

insert into actor 
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33 '),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
--------------------------------------------------------------------------------
insert into actor(actor_id, first_name, last_name, last_update) 
values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')

35.对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

（1）题目：对于表actor批量插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

（2）解法：

因为题目判定系统使用的是sqlite3，所以必须按sqlite3的写法来做，
insert or ignore into actor 
values(3,'ED','CHASE','2006-02-15 12:34:33');

如果是mysql，那么把or去掉，像下面这样：
insert IGNORE into actor 
values(3,'ED','CHASE','2006-02-15 12:34:33');

两种数据库还是有区别的。

36.请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表
（1）题目：对于如下表actor，其对应的数据为:

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下：

（2）解法：

题目使用的是sqlite3，可以这么做：
create table actor_name as 
select first_name,last_name from actor;

如果是mysql，那么as可以去掉，也可以不去掉，例如：
create table actor_name
select first_name,last_name from actor;

--------------------------------------------------------------------------------
CREATE TABLE actor_name(
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL
);
INSERT INTO actor_name 
SELECT first_name, last_name FROM actor;

37.对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

（1）题目：针对如下表actor结构创建索引：(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作)

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname。(请先创建唯一索引，再创建普通索引)。
（2）解法：

MySQL：
ALTER TABLE actor ADD UNIQUE INDEX  uniq_idx_firstname(first_name);
ALTER TABLE actor ADD INDEX idx_lastname(last_name);

SQLite：
CREATE UNIQUE INDEX uniq_idx_firstname ON actor(first_name);
CREATE INDEX idx_lastname ON actor(last_name);

38.针对actor表创建视图actor_name_view

（1）题目：针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
)

（2）解法：本题可用以下两种方法求解，区别在于命名VIEW中字段名的方法差异。另外，本题OJ系统有Bug，由错误提示可以看到，VIEW中本应有字段first_name_v，而OJ系统误设为了fist_name_v。

方法一：注意 CREATE VIEW … AS … 的 AS 是创建视图语法中的一部分，而后面的两个 AS 只是为字段创建别名

CREATE VIEW actor_name_view AS
SELECT first_name AS fist_name_v, last_name AS last_name_v
FROM actor 

方法二：直接在视图名的后面用小括号创建视图中的字段名

CREATE VIEW actor_name_view (fist_name_v, last_name_v) AS
SELECT first_name, last_name FROM actor 

39. 针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引.

（1）题目：针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。

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 index idx_emp_no on salaries(emp_no);

（2）解法：

SQLite中，使用 INDEXED BY 语句进行强制索引查询，可参考：
select * 
from salaries 
indexed by idx_emp_no 
where emp_no = 10005;

MySQL中，使用 FORCE INDEX 语句进行强制索引查询，可参考：
SELECT * 
FROM salaries 
FORCE INDEX idx_emp_no 
WHERE emp_no = 10005;

40.在last_update后面新增加一列

（1）题目：存在actor表，包含如下列信息：

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime'))
);

现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’0000-00-00 00:00:00’
（2）解法：

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

41.构造一个触发器audit_log，在向employees表中插入一条数据的时候，触发插入相关的数据到audit中

（1）题目：构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

（2）解法：
构造触发器时注意以下几点：
1、用 CREATE TRIGGER 语句构造触发器，用 BEFORE或AFTER 来指定在执行后面的SQL语句之前或之后来触发TRIGGER
2、触发器执行的内容写出 BEGIN与END 之间
3、可以使用 NEW与OLD 关键字访问触发后或触发前的employees_test表单记录

CREATE TRIGGER audit_log AFTER INSERT ON employees_test
BEGIN
    INSERT INTO audit VALUES (NEW.ID, NEW.NAME);
END;

42.删除emp_no重复的记录，只保留最小的id对应的记录

（1）题目：删除emp_no重复的记录，只保留最小的id对应的记录。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL
);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

删除后titles_test表为：

（2）解法：

delete from titles_test
where id not in(
    select min(id)
    from titles_test
    group by emp_no
);

43.将所有to_date为9999-01-01的全部更新为NULL,且

（1）题目：将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL
);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

更新后的值:titles_test 表的值：

（2）解法：

update titles_test
set to_date=null,from_date='2001-01-01'
where to_date='9999-01-01';

44.将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,

（1）题目：将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变，使用replace实现。

CREATE TABLE IF NOT EXISTS titles_test (
 id int(11) not null primary key,
 emp_no int(11) NOT NULL,
 title varchar(50) NOT NULL,
 from_date date NOT NULL,
 to_date date DEFAULT NULL
);

 insert into titles_test values
 ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
 ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
 ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
 ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
 ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
 ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
 ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

（2）解法：
本题运用 REPLACE 有两种解法

方法一：全字段更新替换。由于 REPLACE 的新记录中 id=5，与表中的主键 id=5 冲突，故会替换掉表中 id=5 的记录，否则会插入一条新记录（例如新插入的记录 id = 10）。并且要将所有字段的值写出，否则将置为空。可参考：

REPLACE INTO titles_test VALUES (5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')

方法二：运用REPLACE(X,Y,Z)函数。其中X是要处理的字符串，Y是X中将要被替换的字符串，Z是用来替换Y的字符串，最终返回替换后的字符串。以下语句用 UPDATE和REPLACE 配合完成，用REPLACE函数替换后的新值复制给 id=5 的 emp_no。REPLACE的参数为整型时也可通过。可参考：

UPDATE titles_test SET emp_no = REPLACE(emp_no,10001,10005) WHERE id = 5

/** 另外可以利用OJ系统的漏洞，不用 REPLACE 实现 **/

UPDATE titles_test SET emp_no = 10005 WHERE id = 5

45.将titles_test表名修改为titles_2017

（1）题目：将titles_test表名修改为titles_2017。

CREATE TABLE IF NOT EXISTS titles_test (
 id int(11) not null primary key,
 emp_no int(11) NOT NULL,
 title varchar(50) NOT NULL,
 from_date date NOT NULL,
 to_date date DEFAULT NULL
)

 insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
 ('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
 ('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
 ('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
 ('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
 ('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
 ('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

（2）解法：

alter table titles_test rename to titles_2017;

46.在audit表上创建外键约束，其emp_no对应employees_test表的主键id

（1）题目：在audit表上创建外键约束，其emp_no对应employees_test表的主键id。 (audit已经创建，需要先drop) 。(注：创建表的时候，字段的顺序不要改变)

CREATE TABLE employees_test(
 ID INT PRIMARY KEY NOT NULL,
 NAME TEXT NOT NULL,
 AGE INT NOT NULL,
 ADDRESS CHAR(50),
 SALARY REAL
 );
 
 CREATE TABLE audit(
 EMP_no INT NOT NULL,
 create_date datetime NOT NULL
); 

（2）解法：

MySQL：
drop table audit;
create table audit(
    EMP_no int not null,
    create_date daetime not null,
    constraint fk_a_e foreign key(EMP_no) references employees_test(ID)
);

SQLite：
drop table audit;
create table audit(
    EMP_no int not null,
    create_date datetime not null,
    foreign key(EMP_no) references employees_test(ID)
);

48.将所有获取奖金的员工当前的薪水增加10%

（1）题目：请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

create table emp_bonus(
emp_no int not null,
btype smallint not null
);

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

如：
INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');

更新后的结果:

（2）解法：

update salaries
set salary = salary*1.1
where to_date = '9999-01-01' and emp_no in
(select emp_no
from emp_bonus);

49.针对库中的所有表生成select

（1）题目：针对库中的所有表生成select count(*)对应的SQL语句，如数据库里有以下表。(注:在 SQLite 中用 “||” 符号连接字符串，无法使用concat函数)
employees——departments——dept_emp——dept_manage——salaries——titles ——emp_bonus
那么就会输出以下的样子:

（2）解法：

sqlite写法(牛客网通过)： 
select "select count(*) from "||name||";" as cnts
from sqlite_master 
where type='table'; 

mysql写法(牛客网不通过，但是我在自己的mysql上运行通过)： 
select concat("select count(*) from "," ",table_name,";") as cnts
from 
(select table_name from information_schema.tables);

50.将employees表中的所有员工的last_name和first_name通过(’)连接起来。

（1）题目：将employees表中的所有员工的last_name和first_name通过(’)连接起来。(不支持concat，请用||实现)

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

输出格式:
name
Facello’Georgi
Simmel’Bezalel
Bamford’Parto
Koblick’Chirstian
Maliniak’Kyoichi
Preusig’Anneke
Zielinski’Tzvetan
Kalloufi’Saniya
Peac’Sumant
Piveteau’Duangkaew
Sluis’Mary
（2）解法：

select last_name || "'" || first_name from employees;

51.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt

（1）题目：查找字符串’10,A,B’ 中逗号’,‘出现的次数cnt。
（2）解析：
1.通过length函数获得字符串的长度：length( ‘10,A,B’)
2.通过replace函数，用空字符替代掉所求字符，并获得替代后的字符串长度： length(REPLACE( ‘10,A,B’,’,’,’’)
3.两者相减，获得所求字符出现的次数：length( ‘10,A,B’) - length(REPLACE( ‘10,A,B’,’,’,’’));

SELECT length( '10,A,B') - length(REPLACE( '10,A,B',',',''));

52.获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

（1）题目：获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列。

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

输出格式：

（2）解析：本题考查 substr(X,Y,Z) 或 substr(X,Y) 函数的使用。其中X是要截取的字符串。Y是字符串的起始位置（注意第一个字符的位置为1，而不为0），取值范围是±(1~length(X))，当Y等于length(X)时，则截取最后一个字符；当Y等于负整数-n时，则从倒数第n个字符处截取。Z是要截取字符串的长度，取值范围是正整数，若Z省略，则从Y处一直截取到字符串末尾；若Z大于剩下的字符串长度，也是截取到字符串末尾为止。

SELECT first_name FROM employees ORDER BY substr(first_name,length(first_name)-1);
 
SELECT first_name FROM employees ORDER BY substr(first_name,-2);

53.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

（1）题目：按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

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

输出格式:

（2）解析：
本题要用到SQLite的聚合函数group_concat(X,Y)，其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。此函数必须与 GROUP BY 配合使用。此题以 dept_no 作为分组，将每个分组中不同的emp_no用逗号连接起来（即可省略Y）。可参考：

SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp 
GROUP BY dept_no

mysql写法： 
select dept_no,group_concat(emp_no SEPARATOR ',') 
from dept_emp 
group by dept_no;

54.查找排除当前最大、最小salary之后的员工的平均工资avg_salary

（1）题目：查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_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`)
);

如：
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');

输出格式:

（2）解析：

select avg(salary) avg_salary
from salaries
where to_date = '9999-01-01' and salary <>(
    select min(salary)
    from salaries
    where to_date = '9999-01-01' 
) and salary <>(
    select max(salary)
    from salaries
    where to_date = '9999-01-01' 
);

55.分页查询employees表，每5行一页，返回第2页的数据

（1）题目：分页查询employees表，每5行一页，返回第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`)
);

（2）解析：

select *
from employees
limit 5,5; #（n-1）*size,size;

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

（1）题目：获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的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 `emp_bonus`(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL
);

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

返回的结果格式如下:

（2）解析：

select e.emp_no,de.dept_no,eb.btype,eb.received
from employees e 
join dept_emp de 
on e.emp_no = de.emp_no
left join emp_bonus eb
on e.emp_no = eb.emp_no;
--------------------------------------------------------------------------------
select d.emp_no , d.dept_no , eb.btype , eb.received
from dept_emp as d
left join emp_bonus as eb
on d.emp_no = eb.emp_no
where d.emp_no in(
    select emp_no
    from employees
);

57.使用含有关键字exists查找未分配具体部门的员工的所有信息。

（1）题目：使用含有关键字exists查找未分配具体部门的员工的所有信息。

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

输出格式:

（2）解析：
本题可以用EXISTS和IN两种方法：
主表为employees，从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下：当主表比从表大时，IN查询的效率较高；当从表比主表大时，EXISTS查询的效率较高；
原因如下：

• in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次。
• exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

/* 1. EXISTS */
SELECT *
FROM employees
WHERE NOT EXISTS (SELECT emp_no FROM dept_emp WHERE employees.emp_no = dept_emp.emp_no);

/* 2. IN */
SELECT *
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_emp);

59.获取有奖金的员工相关信息

（1）题目：获取有奖金的员工相关信息。

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 `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 emp_bonus(
emp_no int not null,
received datetime not null,
btype smallint not null
);

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

给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
输出格式:

（2）解析：

select e.emp_no,e.first_name,e.last_name,eb.btype,s.salary, 
    (case eb.btype
        when 1 then s.salary*0.1
        when 2 then s.salary*0.2
        else s.salary*0.3
    end) as bonus
from employees e 
join emp_bonus eb on e.emp_no = eb.emp_no
join salaries s on eb.emp_no = s.emp_no
where s.to_date = '9999-01-01';

60.统计salary的累计和running_total

（1）题目：按照salary的累计和running_total，其中running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和，其他以此类推。 具体结果如下Demo展示。

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

输出格式:

（2）解析：

select s1.emp_no,s1.salary,sum(s2.salary) running_total
from salaries s1
join salaries s2 
on s1.emp_no>=s2.emp_no
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
group by s1.emp_no;

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

（1）题目：对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name

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

如输入为：
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');

输出格式:

因为Georgi按first_name排名为3，Anneke按first_name排名为1，所以会输出这2个，且输出时不需排序。
（2）解析：

select e1.first_name
from employees e1
join employees e2 
on e1.first_name >= e2.first_name
group by e1.emp_no
having count(e2.first_name)%2=1;#(有问题)

--------------------------------------------------------------------------------
SELECT e1.first_name 
FROM employees e1
WHERE 
(SELECT count(*) FROM employees e2 
WHERE e1.first_name >=e2.first_name)%2=1;

62.出现三次以上相同积分的情况

（1）题目：在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下:

id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分，查询结果如下:

（2）解析：

select number from grade
group by number
having count(id) >= 3;

63.刷题通过的题目排名

（1）题目：在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:

第1行表示id为1的用户通过了4个题目;
…
第6行表示id为6的用户通过了4个题目;
请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:

id为5的用户通过了5个排名第1，
id为1和id为6的都通过了2个，并列第2
（2）解析：

select pn1.id,pn1.number,count(distinct pn2.number) rank
from passing_number pn1
join passing_number pn2
on pn1.number <= pn2.number
group by pn1.id
order by rank,pn1.id;

64.找到每个人的任务

（1）题目：有一个person表，主键是id，如下:

有一个任务(task)表如下，主键也是id，如下:

请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序，输出情况如下:

（2）解析：

select p.id,p.name,t.content
from person p 
left join task t 
on p.id = t.person_id 
order by p.id;

65.异常的邮件概率

（1）题目：现在有一个需求，让你统计正常用户发送给正常用户邮件失败的概率:
有一个邮件(email)表，id为主键， type是枚举类型，枚举成员为(completed，no_completed)，completed代表邮件发送是成功的，no_completed代表邮件是发送失败的。简况如下:

第1行表示为id为2的用户在2020-01-11成功发送了一封邮件给了id为3的用户;…
第3行表示为id为1的用户在2020-01-11没有成功发送一封邮件给了id为4的用户;…
第6行表示为id为4的用户在2020-01-12成功发送了一封邮件给了id为1的用户;
下面是一个用户(user)表，id为主键，is_blacklist为0代表为正常用户，is_blacklist为1代表为黑名单用户，简况如下:

第1行表示id为1的是正常用户;
第2行表示id为2的不是正常用户，是黑名单用户，如果发送大量邮件或者出现各种情况就会容易发送邮件失败的用户。。。
第4行表示id为4的是正常用户
现在让你写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序，上面例子查询结果如下:

结果表示:
2020-01-11失败的概率为0.500，因为email的第1条数据，发送的用户id为2是黑名单用户，所以不计入统计，正常用户发正常用户总共2次，但是失败了1次，所以概率是0.500;
2020-01-12没有失败的情况，所以概率为0.000.
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
（2）解析：我们这里用一个函数叫case…when …then …else …end
就很简单了，如下:sum(case email.type when ‘completed’ then 0 else 1 end)
这个代码的意思就是，当email.type为completed时，就为0，如果不为completed就为1，然后把这一列所有的和加起来，我们就得到了失败的发送邮件的数目，然后使用round函数保留后面3位:

select e.date,
round(sum(case e.type when 'no_completed' then 1 else 0 end)*1.0/count(e.type),3)
from email e 
join user u1 on e.send_id = u1.id
join user u2 on e.receive_id = u2.id
where u1.is_blacklist = 0 and u2.is_blacklist =0
group by e.date;

66.牛客每个人最近的登录日期(一)

（1）题目：牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网。。。
第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下:

查询结果表明:
user_id为2的最近的登录日期在2020-10-13
user_id为3的最近的登录日期也是2020-10-13
（2）解析：

select user_id,max(date) d 
from login 
group by user_id 
order by user_id;

67.牛客每个人最近的登录日期(二)

（1）题目：牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天，用的是什么设备。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网。。。
第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
还有一个用户(user)表，简况如下:

还有一个客户端(client)表，简况如下:client_id=

请你写出一个sql语句查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序，上面的例子查询结果如下:

查询结果表明:
fh最近的登录日期在2020-10-13，而且是使用pc登录的
wangchao最近的登录日期也是2020-10-13，而且是使用ios登录的
（2）解析：

select u.name u_n,c.name c_name,max(l.date) d 
from login l 
join user u on l.user_id = u.id
join client c on l.client_id = c.id
group by l.user_id
order by u.name;

68.牛客每个人最近的登录日期(三)

（1）题目：牛客每天有很多人登录，请你统计一下牛客新登录用户的次日成功的留存率，
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网。。。
第4行表示id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网。。。
最后1行表示id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网
请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，上面的例子查询结果如下:

查询结果表明:
id为1的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为2的用户在2020-10-12第一次新登录了，在2020-10-13又登录了，算是成功的留存
id为3的用户在2020-10-12第一次新登录了，在2020-10-13没登录了，算是失败的留存
id为4的用户在2020-10-13第一次新登录了，在2020-10-14没登录了，算是失败的留存
固次日成功的留存率为 2/4=0.5
(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
（2）解析：
（第一天登录的新用户并且第二天也登录的用户）/（总用户）即为新登录用户的次日成功的留存率
总用户其实挺好算，如下：

select count(distinct user_id) from login

找到每个用户第一天登陆的日子，其实挺好找，和前面找最近登录的日子差不多，一个是max，一个是min:

select user_id,min(date) from login group by user_id

比如上面查找语句是1，2020-10-12；那么如果找到一个结果为1，2020-10-13的那么是不是就符合结果了，于是可以如下写:

select user_id,date(min(date),'+1 day') from login group by user_id

这样就可以找到所有的在第一天登录的新用户并且第二天也登录的用户，以及第二天的日期。
所以从这个里面找到所有的count(distinct user_id)除以总用户就可以得到结果了，于是整个sql语句如下:

select 
round(count(distinct user_id)*1.0/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,date(min(date),'+1 day') from login group by user_id);

69.牛客每个人最近的登录日期(四)

（1）题目：牛客每天有很多人登录，请你统计一下牛客每个日期登录新用户个数，
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户。。。
第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户。。
最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:(输出0，可以用sqlite的ifnull函数尝试实现，select ifnull(null,1)的输出是1)

查询结果表明:
2020-10-12，有3个新用户(id为2，3，1)登录
2020-10-13，没有新用户登录
2020-10-14，有1个新用户(id为4)登录
2020-10-15，没有新用户登录
（2）解析：
这样为什么不对？因为13号没有一个新用户，你的第一个表中就没有13号这条记录
思路：1. 先按照用户ID分组，取出每个ID对应的最早的登录日期存到一个表里，此时表里存放的是所有用户成为新用户的日期。
2. 然后再按日期分类级数就是每个日期中有多少新用户

select g.date,count(*) new
from 
(select user_id,min(date) date
from login 
group by user_id) g 
group by date
order by date;

---

这个题目做过前面的题的话，应该可以比较容易就知道，
select l1.date,count(distinct l1.user_id)from login l1group by l1.date;

这样可以得到每个日期里面，用户登录的数目，比较简单，所以在加一个where判断条件就能从这每个日期里面，用户登录的数目取出哪些是新用户，如下：
select l1.date,count(distinct l1.user_id)from login l1where l1.date =(select min(date) from login where user_id=l1.user_id)group by l1.date;

当这个日期，正好是这个用户登录的最小日期，而且用户id相同时，那么肯定就是这个日期登录的新用户，执行的用例的话，得到的结果应该如下:
2020-10-12|3
2020-10-14|1

但是这样并不能通过用例，因为这样的话，2020-10-13没有新用户登录，应该输出为0的，这个语句却没有输出。但是login表的日期是完整的，所以我们考虑将login表当主表，上面查出来的表左连接到主表，顺序输出，并使用ifnull语句将null变成0，最后再加上一个order by语句，就可以得到题目想要的结果了:

select login.date,ifnull(n1.new_num,0)
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date = (select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date 
order by login.date;

---

思路是窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可。

select a.date,sum(case when rank=1 then 1 else 0 end) new
from 
(select date, row_number() over(partition by user_id order by date) rank
from login) a
group by date;

70.牛客每个人最近的登录日期(五)

（1）题目：牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网，因为是第1次登录，所以是新用户。。。
第4行表示id为2的用户在2020-10-13使用了客户端id为2的设备登录了牛客网，因为是第2次登录，所以是老用户。。
最后1行表示id为4的用户在2020-10-15使用了客户端id为1的设备登录了牛客网，因为是第2次登录，所以是老用户
请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序，上面的例子查询结果如下:

查询结果表明:
2020-10-12登录了3个(id为2，3，1)新用户，2020-10-13，只有2个(id为2,1)登录，故2020-10-12新用户次日留存率为2/3=0.667;
2020-10-13没有新用户登录，输出0.000;
2020-10-14登录了1个(id为4)新用户，2020-10-15，id为4的用户登录，故2020-10-14新用户次日留存率为1/1=1.000;
2020-10-15没有新用户登录，输出0.000;
(注意:sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, ‘+1 day’)，sqlite里1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)
（2）解析：
这个题目如果你做过牛客每个人最近的登录日期(三)和牛客每个人最近的登录日期(四)，那么解决起来会容易很多，做过(四) https://blog.nowcoder.net/n/a2f955514f824bb888f9d7726421e809我们知道每个日期的新用户求法为:

select login.date,ifnull(n1.new_num,0)
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date) n1
on login.date = n1.date
group by login.date

做过(三) https://blog.nowcoder.net/n/1dad24440b3e45949dfda03cb1f06a9e的话，我们可以轻松得到每个日期新用户次日还登录的人的个数的sql语句如下:

select l1.date,count(distinct l1.user_id)
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =(select min(date) from login where user_id=l1.user_id)
group by l1.date;

得到的结果如下:

2020-10-12|2
2020-10-14|1

又在(四)里面提到过联立主表，将查询次日还登录的人的sql也联立主表：

(select login.date,ifnull(n1.new_num,0) as second_login_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) second_login

就可以得到:

2020-10-12|2
2020-10-13|0
2020-10-14|1
2020-10-15|0

的结果了，然后将这2个表联立相除，得到的结果保留3为，用ifnull判断0/0的情况，那么就可以得到完整结果了：

select second_login.date, round(ifnull(second_login.second_login_num *1.0/ first_login.first_num,0),3)
from (select login.date,ifnull(n1.new_num,0) as second_login_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') 
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) second_login
 
join 
 
(select login.date,ifnull(n1.new_num,0) as first_num
from login 
left join 
(select l1.date,count(distinct l1.user_id) as new_num
from login l1
where l1.date =
(select min(date) from login where user_id=l1.user_id)
group by l1.date) n1
on login.date = n1.date
group by login.date) first_login
 
on second_login.date=first_login.date;

71.牛客每个人最近的登录日期(六)

（1）题目：牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。 不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
有一个登录(login)记录表，简况如下:

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网。。。
第5行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网
有一个刷题（passing_number)表，简况如下:

第1行表示id为2的用户在2020-10-12通过了4个题目。。。。
第3行表示id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示id为4的用户在2020-10-13通过了2个题目
还有一个用户(user)表，简况如下:

请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:

查询结果表明:
fh在2020-10-12为止，总共通过了4道题，输出为4
wangchao在2020-10-12为止，总共通过了1道题，总计为1
tm在2020-10-12为止只登陆了没有刷题，故没有显示出来
tm在2020-10-13为止刷了题，但是却没有通过任何题目，总计为0
wangchao在2020-10-13通过2道，但是加上前面2020-10-12通过1道，故在2020-10-13为止总共通过了3道题，总计为3
（2）解析：

select user_id,date,sum(number) over(partition by user_id order by date) from passing_number

与

select pn1.user_id,pn1.date,sum(pn2.number) 
from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date 
group by pn1.user_id,pn1.date;

这2种方法，均可以得到某一天，id为多少的用户，在这一天和这一天之前，通过的题目总数，id和日期，然后和登录(login)表，用户(user)表连接，再按照题目要求的东西排序，就可以得到最后的结果了:

select user.name as u_n, client.name as c_n, login.date, p1.ps_num
from login 
join (select user_id, date ,sum(number) over(partition by user_id order by date) ps_num from passing_number) p1
on p1.user_id=login.user_id and p1.date=login.date
join user on login.user_id=user.id
order by login.date, user.name

与以下语句等价:

select user.name as u_n, client.name as c_n, login.date, p1.ps_num
from login 
join (select pn1.user_id,pn1.date,sum(pn2.number) as ps_num
from passing_number pn1,passing_number pn2 where pn1.user_id=pn2.user_id and pn1.date>=pn2.date 
group by pn1.user_id,pn1.date) p1
on p1.user_id=login.user_id and p1.date=login.date
join user on login.user_id=user.id
order by login.date,user.name;

72.考试分数(一)

（1）题目：牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
（2）解析：

select job,round(avg(score),3) avg
from grade 
group by job 
order by avg desc;

73.考试分数(二)

（1）题目：牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)
（2）解析：

select id,g.job,g.score
from grade g
join 
(select job, avg(score) avg
from grade 
group by job) d
on g.job = d.job
where g.score > d.avg
order by id ;

--------------------------------------------------------------------------------
SELECT *
FROM grade g1
WHERE score > (SELECT AVG(score)
              FROM grade g2
              WHERE g2.job = g1.job
              GROUP BY job)
ORDER BY id;

74.考试分数(三)

（1）题目：牛客每次举办企业笔试的时候，企业一般都会有不同的语言岗位，比如C++工程师，JAVA工程师，Python工程师，每个用户笔试完有不同的分数，现在有一个分数(grade)表简化如下:

第1行表示用户id为1的选择了language_id为1岗位的最后考试完的分数为12000，…
第7行表示用户id为7的选择了language_id为2岗位的最后考试完的分数为11000，
不同的语言岗位(language)表简化如下:

请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

（2）解析：
主要是用了窗口函数 DENSE_RANK（）

select g.id, l.name, g.score
from (select *, dense_rank() over(partition by language_id order by score desc) as rank 
      from grade) g, language l
where g.language_id = l.id and g.rank <= 2
order by l.name asc, g.score desc, g.id asc;

--------------------------------------------------------------------------------
select l1.id,l3.name,l1.score
from grade as l1
join(
select t1.id
from grade as t1
join grade as t2 on t1.language_id = t2.language_id and t1.score<=t2.score
group by t1.language_id,t1.id
having count(distinct t2.score)<3) as l2 on l1.id = l2.id
join language as l3 on l1.language_id = l3.id
order by l3.name asc,l1.score desc,l1.id asc;

75.考试分数(四)

（1）题目：牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序，结果如下:

解释:
第1行表示C++岗位的中位数位置范围为[2,2]，也就是2。因为C++岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的10000是中位数)
第2行表示Java岗位的中位数位置范围为[1,2]。因为Java岗位总共2个人，是偶数，所以要知道中位数，需要知道2个位置的数字，而因为只有2个人，所以中位数位置为[1,2]是正确的(即需要知道位置为1的12000与位置为2的13000才能计算出中位数为12500)
第3行表示前端岗位的中位数位置范围为[2,2]，也就是2。因为前端岗位总共3个人，是奇数，所以中位数位置为2是正确的(即位置为2的11000是中位数)
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数)
（2）解析：

select job,
case count(*)%2 when 1 then (count(*)/2+1) else count(*)/2 end as start,
(count(*)/2+1)as end
from grade 
group by job
order by job;

76.考试分数(五)
（1）题目：牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：
第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2
第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1
第4行表示前端岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2
(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数，sqlite不支持自定义变量)
（2）解析：
基于中位数的定义，即中位数为一串数字排序后位于正中的数字（参与排序的总数为奇数，中位数仅有1个；参与排序的总数为偶数，中位数有两个）

select id, job, score,dn rank
from
    (select id,job,score,
        rank() over(partition by job order by score) rn,--正序位置
        rank() over(partition by job order by score desc) dn,--倒序位置
        count() over(partition by job) cnt--参与排序总数
    from grade)
where (cnt%2=1 and rn = dn) -- 若排序总数为奇数，则中位数的正序位置与倒序位置相等
     or (cnt%2=0 and abs(rn-dn)=1) --若排序总数为偶数，则中位数的正序位置与倒序位置正好差1
order by id;

---

通过考试分数(四) https://blog.nowcoder.net/n/64e552f2359141f1bc730f00dad84cb9 我们知道，中位数的位置信息，也就是升序之后的排名信息，得到A表:

(select job, cast((count(id)+1)/2 AS INTEGER) as 'start' , (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as 'end'
from grade 
group by job) A

又通过考试分数(三) https://blog.nowcoder.net/n/297da9871a8f4fd39ad939cbfc907093 知道，求不同工作(job)里面的每个人的信息与排名，可以如下写，得到B表:

(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as rank
from grade g1 ) B

大概得到的情况如下:
所以 B表里面有所有的信息，但是要提取中位数的信息，恰好A有中位数的信息，那么联立A，B表，并且当工作(job)相同并且，B的排名(rank)在A表的中位数位置之间，那么就表明这个信息是中位数的信息，最后联立得:

select B.* from
(select job, 
	cast((count(id)+1)/2 AS INTEGER) as 'start' ,
 	(cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as 'end'
	from grade 
	group by job) A
 
JOIN
(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as rank
from grade g1 ) B
 
on (A.job=B.job  and B.rank between A.start and A.end)
order by B.id

Note：部分解答整合了牛客讨论区中的内容，如有不妥，请联系本人删除。