牛客数据库SQL实战 1-20（distinct/group by去重、limit 1,1的使用、select的运算）

最新推荐文章于 2022-10-20 18:38:24 发布

文泽路小男孩

最新推荐文章于 2022-10-20 18:38:24 发布

阅读量1.1k

点赞数 1

分类专栏：数据库文章标签：数据库 SQL

本文链接：https://blog.csdn.net/weixin_43508544/article/details/106000885

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

3 查找当前薪水详情以及部门编号dept_no
- 3.1 题目描述
- 3.2 题解
6 查找所有员工入职时候的薪水情况
- 6.1 题目描述
- 6.2 题解
7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
- 7.1 题目描述
- 7.2 题解
8 找出所有员工当前薪水salary情况
- 8.1 题目描述
- 8.2 题解
- - 8.2.1 distinct 大数据量禁用
  - 8.2.2 group by 建议
10 获取所有非manager的员工emp_no
- 10.1 题目描述
- 10.2 题解
11 获取所有员工当前的manager
- 11.1 题目描述
- 11.2 题解
12 ！获取所有部门中当前员工薪水最高的相关信息
- 12.1 题目描述
- 12.2 题解
13 从titles表获取按照title进行分组
- 13.1 题目描述
- 13.2 题解
14 从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略
- 14.1 题目描述
- 14.2 题解
15 查找employees表
- 15.1 题目描述
- 15.2 题解
16 统计出当前各个title类型对应的员工当前薪水对应的平均工资
- 16.1 题目描述
- 16.2 题解
17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
- 17.1 题目描述
- 17.2 题解 limit 1,1
18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by
- 18.1 题目描述
- 18.2 题解
19 查找所有员工的last_name和first_name以及对应的dept_name
- 19.1 题目描述
- 19.2 题解
20 查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth
- 20.1 题目描述
- 20.2 题解

欢迎访问笔者个人技术博客： http://rukihuang.xyz/
牛客网数据库SQL实战

3 查找当前薪水详情以及部门编号dept_no

3.1 题目描述

查找各个部门当前(to_date=‘9999-01-01’)领导当前薪水详情以及其对应部门编号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`));

在这里插入图片描述

3.2 题解

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

这里的坑主要在于两个表的逻辑关系，题目要求是薪水情况以及部门编号，再结合输出情况dept_no 被放到了最后一列，可见是主表是“salaries”。这里顺序错了就会提示：您的代码无法通过所有用例。。。(个人觉得两张表没有主次之分，主要是from_date在两张表都存在，且有可能数据有差异，主要查的是薪水，因此from_date用的是salaries这张表中的数据)

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

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

在这里插入图片描述

6.2 题解

select 
    e.emp_no
    ,s.salary 
from 
    employees as e
left join 
    salaries as s
on 
    e.emp_no = s.emp_no
where 
    e.hire_date = s.from_date
order by 
    e.emp_no desc

有两个隐含条件
- 一个员工可能有多条salary记录（涨薪、调薪）
- 条件是查入职时候的薪水，并以emp_no倒序

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

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

在这里插入图片描述

7.2 题解

select 
    tmp.*
from
(select 
    emp_no
    ,count(*) t
from 
    salaries
group by
    emp_no) tmp
where t > 15;

拆解为两个步骤
- 计算涨薪的次数
- 条件筛选>15的emp

8 找出所有员工当前薪水salary情况

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

在这里插入图片描述

8.2 题解

8.2.1 distinct 大数据量禁用

select 
    distinct salary
from
    salaries
where 
    to_date = '9999-01-01'
order by 
    salary desc

对于distinct与group by的使用:
- 当对系统的性能高并数据量大时使用group by
- 当对系统的性能不高时使用数据量少时两者皆可
- 尽量使用group by

8.2.2 group by 建议

select 
    salary
from
    salaries
where 
    to_date = '9999-01-01'
group by
    salary
order by 
    salary desc

将相同数据分为一组，再降序

10 获取所有非manager的员工emp_no

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

在这里插入图片描述

10.2 题解

select
    emp_no
from 
    employees
where 
   emp_no not in (
    select 
        emp_no
    from
        dept_manager
    );

11 获取所有员工当前的manager

11.1 题目描述

获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date=‘9999-01-01’。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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`));

11.2 题解

select 
    e.emp_no as emp_no
    ,m.emp_no as manager_no
from 
    dept_emp e
    ,dept_manager m
where 
    e.dept_no = m.dept_no
and 
    e.emp_no <> m.emp_no
and 
    m.to_date = '9999-01-01';

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

12.1 题目描述

获取所有部门中当前员工薪水最高的相关信息，给出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`));

在这里插入图片描述

12.2 题解

参考题解

SELECT 
	r1.dept_no
	,r1.emp_no
	,r1.salary 
FROM
    --创建r1表用于存放当前每个部门每个员工的薪水
    (
    SELECT 
        d.dept_no
        ,d.emp_no
        ,s1.salary
    FROM dept_emp d, salaries s1
    WHERE d.to_date='9999-01-01' 
        AND s1.to_date='9999-01-01'
        AND d.emp_no = s1.emp_no) r1
JOIN  --创建r2表用于存放当前每个部门薪水的最大值
    (
    SELECT d.dept_no, MAX(s2.salary) as maxsalary
    FROM dept_emp d
    --为了避免GROUP BY默认取非聚合数据的第一条记录，先把salary排好序
    JOIN (SELECT * FROM salaries ORDER BY salary DESC) s2
    ON d.emp_no = s2.emp_no
    WHERE d.to_date='9999-01-01' 
        AND s2.to_date='9999-01-01'
    GROUP BY d.dept_no)r2
ON r1.salary = r2.maxsalary
    AND r1.dept_no = r2.dept_no
ORDER BY r2.dept_no

我的题解

select 
	cur_sal.dept_no
	,cur_sal.emp_no
	,cur_sal.salary as salary
from 
    (-- 找到部门最高的薪资
    select 
        dept_no
        ,max(salary) salary
    from
        dept_emp d1
        , salaries s1
    where 
        d1.emp_no = s1.emp_no
    and 
        d1.to_date = '9999-01-01'
    and 
        s1.to_date = '9999-01-01'
    group by 
        dept_no) max_sal
join	
    (-- 目前薪资
    select 
        d2.dept_no
        ,d2.emp_no
        ,s2.salary
    from 
        dept_emp d2
        ,salaries s2
    where
        d2.emp_no = s2.emp_no
    and 
        d2.to_date = '9999-01-01'
    and 
        s2.to_date = '9999-01-01') cur_sal
on 
	max_sal.dept_no = cur_sal.dept_no
where 
	max_sal.salary = cur_sal.salary
order by
	max_sal.dept_no

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

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

13.2 题解

select 
	title 
	,t 
from 
(-- 对title分组并计数
select 
	title
	,count(*) as t
from 
	titles
group by 
	title) tmp
where 
	tmp.t >= 2

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

14.1 题目描述

从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_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);

14.2 题解

正确题解

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

我的思路：之前的sql题说过（8.2.2），去重最好用group by，因为distinct消耗资源过多

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

这种是有问题的，GROUP BY 默认取非聚合的第一条记录！！！！！！

在这里插入图片描述

另外，同一个员工可能在不同的部门，因此，不能group by emp_no，防止title只取默认第一条

在这里插入图片描述

15 查找employees表

15.1 题目描述

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

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

15.2 题解

select 
	*
from 
	employees
where 
	emp_no % 2 <> 0
and 
	last_name <> 'Mary'
order by
	hire_date desc

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

16.1 题目描述

统计出当前各个title类型对应的员工当前（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);

16.2 题解

select 
	title
	,avg(salary)
from
	(-- 1 先把当前的工资和title查出来
	select 
		*
	from 
		salaries s
		,titles t
	where 
		s.emp_no = t.emp_no
	and
		s.to_date = '9999-01-01'
	and 
		t.to_date = '9999-01-01') tmp
group by
	title

17 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

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

17.2 题解 limit 1,1

-- 2 找员工
select 
	emp_no
	,salary
from 
	salaries
where 
	salary = 
(-- 1 先找到第二多的薪水
select 
	distinct salary
from 
	salaries
order by 
	salary desc 
limit 1,1)

在这里插入图片描述

18 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary，不准使用order by

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

18.2 题解

-- 在非最高薪水中，找最高薪水
select
	e.emp_no
	,max(salary)
	,last_name
	,first_name
from 
	employees e
	,salaries s
where 
	e.emp_no = s.emp_no
and
	s.to_date = '9999-01-01'
and 
	salary not in
    (-- 1 找到最高的薪水
    select
        max(salary)
    from 
        employees e
        ,salaries s
    where 
        e.emp_no = s.emp_no
    and
        s.to_date = '9999-01-01')

19 查找所有员工的last_name和first_name以及对应的dept_name

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

19.2 题解

select 
	last_name
	,first_name
	,dept_name
from 
	employees t1
left join
	dept_emp t2 
on 
	t1.emp_no = t2.emp_no
left join	
	departments t3
on 
	t2.dept_no = t3.dept_no

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

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

20.2 题解

我的题解，最高薪和最低薪的差值是涨幅

select 
    (max(salary) - min(salary)) as 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

文泽路小男孩

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
牛客数据库SQL实战 1-20（distinct/group by去重、limit 1,1的使用、select的运算）

文章目录3 查找当前薪水详情以及部门编号dept_no3.1 题目描述3.2 题解6 查找所有员工入职时候的薪水情况6.1 题目描述6.2 题解7 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t7.1 题目描述7.2 题解8 找出所有员工当前薪水salary情况8.1 题目描述8.2 题解8.2.1 distinct 大数据量禁用8.2.2 group by 建议10 获取所有非...
复制链接

扫一扫