每天几道SQL（3）

于 2020-03-29 16:20:57 发布
阅读量154 收藏
点赞数
文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44346329/article/details/105174850
版权

老规矩，还是先来10道牛客网的题
21-30
21.题目描述查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
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));

select s1.emp_no,(s1.salary-s2.salary) as growth
from (select s.salary ,e.emp_no from salaries s,employees e
      where s.to_date = '9999-01-01'
     and e.emp_no = s.emp_no) s1,
     (select s.salary ,e.emp_no from salaries s ,employees e
      where s.from_date=e.hire_date
       and s.emp_no = e.emp_no) s2
where s2.emp_no = s1.emp_no
order by growth

22.统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数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));
代码：

select d.dept_no,d.dept_name,count(s.emp_no)
from departments d,dept_emp de, salaries s
where d.dept_no = de.dept_no
and de.emp_no = s.emp_no
group by d.dept_no

23.题目描述对所有员工的当前(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));

思路：进行排名就是数比我工资高的人有几个，salary存在相等的情况，就数distinct有几个，要用group by分组是因为是对每一个员工数比他工资高的有几个，如果不分组，会输出s2比s1 salary高的所有数目

select s1.emp_no,s1.salary,count(distinct s2.salary) as rank
from salaries s1,salaries s2
where s2.salary>=s1.salary
and 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

24.获取所有非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));

其实不需要employees 表的信息，三个表就足够了
代码：

select de.dept_no,de.emp_no,s.salary
from dept_emp de,salaries s
where de.emp_no not in (select emp_no from dept_manager)
and de.emp_no=s.emp_no
and de.to_date='9999-01-01'
and s.to_date='9999-01-01'

25.获取员工其当前的薪水比其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));

将工资表和两个员工表分别连接

代码：

select es.emp_no,ms.emp_no as manager_no,es.salary,ms.salary
from
(select s.salary,de.emp_no,de.dept_no from salaries s ,dept_emp de
where de.emp_no=s.emp_no
and s.to_date='9999-01-01'
and de.emp_no not in (select emp_no from dept_manager)) as es,
(select s.salary,dm.emp_no,dm.dept_no from salaries s ,dept_manager dm
where dm.emp_no=s.emp_no
and s.to_date='9999-01-01') as ms
where es.dept_no=ms.dept_no
and es.salary>ms.salary

26.汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count
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);

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

27.给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列。 提示：在sqlite中获取datetime时间对应的年份函数为strftime(’%Y’, to_date)
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));

如何理解每年的涨幅？
参考题解中网友的回答，一张原始表，一张比较表，对应每一个员工来说，一年可能涨一次薪水，可能涨多次薪水，from_date相差1或者to_date相差1都是表示相差1年，这个弯我自己不参考别人的是转不过来。

SELECT s2.emp_no, s2.from_date, (s2.salary - s1.salary) AS salary_growth
FROM salaries AS s1, salaries AS s2
WHERE s1.emp_no = s2.emp_no 
AND salary_growth > 5000
AND (strftime("%Y",s2.to_date) - strftime("%Y",s1.to_date) = 1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1 )
ORDER BY salary_growth DESC

获取时间函数
DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY
Date（）返回日期
NOW（）返回日期和时间
CurDate（）日期
CurTime（）时间
Datediff(year，Now（），‘2018-01-02’)返回时间差

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

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

1） 描述信息里包括robot的电影，用到film表，like字段
2） 对应分类名称需要连接film_category表找到ca_id
3） film_ca表再和category表连接获得category name
4） 该分类电影数目在f_c表里进行count筛选

看一下下面两段代码

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

区别在于代码1表达的是包含robot的对应的电影类型的电影数量大于等于5，robot电影超过5部
代码2表示的是数量大于等于五部电影的那些电影类型，这些电影不一定包含robot，要求的是里面包含robot的是哪些，他们的名称具体是什么
我说的乱七八糟的，具体题意是第二种

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

不需要用到第二个表，需要用到f表和fc表，将他们join起来，查询category_id为null的那些filmid和title

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

先从c表选出action类，获得分类id
将这个子表和fc表连接获得电影id
最后和f表连接，获得title和description

select f.title,f.description from
film f,film_category fc,
(select * from category c where c.name like '%Action%' ) ac
where ac.category_id = fc.category_id
and fc.film_id = f.film_id

今天周天，不想做额外的题了,嘻嘻

Kelly会有糖吃的
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值