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

Author： ruki_in_HDU
发布时间：May 8, 2020
2267views
No comments
14234 words
Categories： Tech

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

Last modification：May 8th, 2020 at 04:56 pm

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

ruki_in_HDU • 2020 年 05 月 08 日

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

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 题目描述

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

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 题解