☺☺☺
Day2六道题
考查知识点
题号 | 知识点 |
---|---|
1 | HAVING 用来对GROUP BY 之后的数据进行限制 |
2 | DISTINCT 去重; ORDER BY 排序; DESC 降序排列 |
3 | DISTINCT 去重;WHERE 链接两表 |
4 | [A] NOT IN [SELECT B...] 在数据A中将B去除 |
5 | 考察not in |
6 | 考察GROUP BY 聚合函数的用法 |
一、查找薪水变动超过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
));
- 分析:
以员工号为准进行聚合,找到每个员工的薪水即salary的变动次数进行count,大于15次从HAVING对聚合之后的salary次数进行限制 - 代码:
SELECT emp_no, count(salary) as t
FROM salaries
GROUP BY emp_no
HAVING t>15
二、找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
- 题目描述:
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况,对于相同的薪水只显示一次,并按照逆序显示
CREATE TABLEsalaries
(
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
));
- 分析:
- 使用
WHERE
来对时间进行限制 - 使用
DISTINCT
来对薪水salary这列数据进行去重 - 使用
ORDER BY
来对薪水一列数据排序,使用DESC
来降序排列
- 代码:
SELECT DISTINCT salary
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary
DESC
三、获取所有部门当前manager的当前薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
- 题目描述:
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况,给出dept_no, emp_no以及salary(请注意,同一个人可能有多条薪水情况记录)
CREATE TABLEdept_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 TABLEsalaries
(
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
));
- 分析:
- 使用
WHERE
来对时间进行限制; - 使用
WHERE
使得两个表的emp_no值相同 - 使用
DISTINCT
去重。
- 代码:
SELECT a.dept_no, a.emp_no, b.salary
FROM dept_manager a, salaries b
WHERE a.to_date='9999-01-01'
AND b.to_date='9999-01-01'
AND a.emp_no = b.emp_no
四、获取所有非manager的员工emp_no
- 题目描述:
获取所有非manager的员工emp_no
CREATE TABLEdept_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 TABLEemployees
(
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
));
- 分析:
考察...NOT IN (SELECT data FROM table)
- 代码:
SELECT emp_no
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
五、获取所有员工当前的manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no
- 题目描述:
获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
CREATE TABLEdept_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 TABLEdept_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
));
- 分析:
- 使用
WHERE
对时间限制; - 去除本身是manager的数据用
not in
- 代码:
SELECT a.emp_no, b.emp_no
FROM dept_emp a, dept_manager b
WHERE a.dept_no = b.dept_no
AND b.to_date='9999-01-01'
AND a.emp_no not in (SELECT emp_no FROM dept_manager)
六、获取所有部门中当前员工当前薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
- 题目描述:
获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary
CREATE TABLEdept_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 TABLEsalaries
(
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
));
- 分析:
- 最高用max
- 以下代码虽通过全部用例,但总觉得不对,group by将表salaries以部门为准则进行聚合,然后max是找到了每个部门的最大值输出了,但是并没有和emp_no相对应起来。dept_no的输出满足了WHERE的条件并与salary是匹配的因为dept_no是聚合的参数,但输出的emp_no和salary并不匹配。
- 经过实际mysql操作后也发现返回的emp_no值与薪水salary最大值不匹配。
原因(from nowcoder,作者:xiling):使用group by子句时,select子句中只能有聚合键、聚合函数、常数。
emp_no并不符合这个要求。
- 错误代码,但通过全部测试用例了:
SELECT a.dept_no, a.emp_no, MAX(b.salary)
FROM dept_emp a, salaries b
WHERE a.to_date = ‘9999-01-01’
AND b.to_date=‘9999-01-01’
AND a.emp_no = b.emp_no
GROUP BY a.dept_no
- 正确代码:
思路:先找到这个符合WHERE条件的salary的最大值,然后再去匹配这个最大值所在的dept_no与emp_no。
select de.dept_no, de.emp_no, s.salary
from dept_emp de inner join salaries s
on de.emp_no = s.emp_no
and de.to_date = '9999-01-01'
and s.to_date = '9999-01-01'
where s.salary = (select max(s2.salary)
from dept_emp de2 inner join salaries s2
on de2.emp_no = s2.emp_no
and de2.to_date = '9999-01-01'
and s2.to_date = '9999-01-01'
where de2.dept_no = de.dept_no
group by de2.dept_no)
order by de.dept_no
- 笔记:
GROUP BY
语句可结合一些聚合函数来使用。GROUP BY
语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
语法:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;