牛客SQL编程SQ23-SQL28

SQL23.对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列
用dense_rank()函数按照salary降序，再以t_rank升序排序，相同工资以emp_no升序排序，t_rank在前，emp_no在后，顺序不可以变

select emp_no,salary,dense_rank() over (order by salary desc) as t_rank
from salaries
order by t_rank,emp_no

排序窗口函数
rank():如果存在相同位次，会跳过之后的位次。例如，有两个相等，且排名第二。1,2,2,4…
dense_rank():如果存在相同位次，不会跳过之后的位次。例如，有两个相等，且排名第二。1,2,2,3…
row_number():不存在相同位次，会赋予连续唯一位次。例如，有两个相等，且排名第二。1,2,3,4…

SQL24.获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary
连接dept_emp和salaries表，查出员工的相关信息，并用not in去掉管理者员工。

select d.dept_no,d.emp_no,s.salary
from dept_emp d join salaries s
on d.emp_no=s.emp_no
where d.emp_no not in(select emp_no
                     from dept_manager)

SQL25. 获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary
查询当前经理工资表m，再查询当前非管理员工资表e，连接两表，找出员工的工资比其经理还高的相关信息

select e.emp_no,m.emp_no,e.salary,m.salary
from (select dm.emp_no,dm.dept_no,s.salary
 from dept_manager dm join salaries s
 on dm.emp_no=s.emp_no
 and dm.to_date='9999-01-01'
 and s.to_date='9999-01-01')as m
 join
 (select de.emp_no,de.dept_no,s.salary
 from dept_emp de join salaries s
 on de.emp_no=s.emp_no
 where de.emp_no not in(select emp_no from dept_manager)
 and de.to_date='9999-01-01'
 and s.to_date='9999-01-01')as e
 on m.dept_no=e.dept_no
 where e.salary>m.salary

查询当前员工工资表s1,当前经理工资表s2，条件限制部门内经理的工资和其员工比较

select de.emp_no as emp_no,dm.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp de,dept_manager dm,salaries s1,salaries s2
where de.dept_no=dm.dept_no//限制在同一个部门内
and de.emp_no=s1.emp_no
and dm.emp_no=s2.emp_no
and s1.salary>s2.salary
and s1.to_date='9999-01-01'
and s2.to_date='9999-01-01'

SQL26. 汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序
联合三表查询，重点是嵌套分组，先按部门号分组，再进一步按头衔分组

select de.dept_no,d.dept_name,t.title,count(title)as count
from dept_emp de,departments d,titles t
where de.dept_no=d.dept_no
and de.emp_no=t.emp_no
and de.to_date='9999-01-01'
group by de.dept_no,t.title
order by de.dept_no

SQL28.查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部
连接三个表，查询电影描述里包含robot的对应的电影分类名称以及电影数目，并要求电影分类包含的数目>=5的结果,用in关键字实现

select c.name,count(fc.film_id)
from film f,category c,film_category fc
where f.description like '%robot%'
and fc.film_id=f.film_id
and fc.category_id=c.category_id
and fc.category_id in(select fc.category_id
                  from film_category fc
                  group by fc.category_id
                  having count(fc.category_id)>=5)  

字符匹配
% 匹配零个或多个字符
_ 匹配单个字符
/ 转义字符，将特殊字符转为普通字符。 /%去匹配%