牛客SQL编程SQL57-SQL64

SQL57.使用含有关键字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));
解题思路：没有分配部门的员工不存在于dept_emp表中，找出不存在于dept_emp表的员工的记录

select * from employees
where not exists (select emp_no from dept_emp
                 where employees.emp_no=dept_emp.emp_no);

SQL59.获取有奖金的员工相关信息,给出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。 bonus类型btype为1其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。 当前薪水表示to_date=‘9999-01-01’
解题思路：其他的只需要连接表即可，bonus可以使用case语句进行分类

select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
case when b.btype=1 then s.salary*0.1
when b.btype=2 then s.salary*0.2
when b.btype=3 then s.salary*0.3
end bonus
from employees as e,emp_bonus as b,salaries as s
where e.emp_no=b.emp_no
and e.emp_no=s.emp_no
and s.to_date="9999-01-01";

SQL60.按照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));
解题思路：把sum聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。
sum(<汇总列>) over(<排序列>) as 别名；

select emp_no,salary,sum(salary) over(order by emp_no) as running_total
from salaries
where to_date="9999-01-01";

SQL61.对于employees表中，输出first_name排名(按first_name升序排序)为奇数的first_name，且输出时不需排序
解题思路：使用窗口函数，题目要求输出不排序，所以在子查询中查出first_name的排名，再连接employees表输出结果

select e.first_name
from employees as e join
    (select first_name,
     row_number() over(order by first_name) as r_rank
     from employees
    )as a
on e.first_name=a.first_name
where a.r_rank%2=1;

还可以使用count函数，小于等于e1的first_name的只有它本身和前面的first_name，记录数量加起来等于他自己的排名

elect e1.first_name
from employees as e1
where (select count(*) from employees as e2 
      where e1.first_name>=e2.first_name)%2=1

SQL62.在牛客刷题的小伙伴们都有着牛客积分，积分(grade)表简化可以如下:id为用户主键id，number代表积分情况，让你写一个sql查询，积分表里面出现三次以及三次以上的积分
解题思路：使用group by子句分组，在使用having子句筛选结果

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

SQL63.在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列
解题思路：使用窗口函数，按number逆序排序得出排名
排序详解
情况：第一条和第二条记录相同，第三条和第四条记录相同
rank函数：在排序时，如果存在相同位次的记录，会跳过之后的位次，比如1,1,3,3…
dense_rank函数：在排序时，如果存在相同位次的记录，却不会跳过之后的位次，比如1,1,2,2…
row_number函数：赋予唯一的连续位次，比如1,2,3,4…

select id,number,dense_rank() over(order by number desc) as t_rank
from passing_number
order by t_rank,id;

SQL64.请你找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序
解题思路：使用左连接，并按照person的id升序排序。

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