文章目录
- 11、获取所有员工当前的(dept_manager.to_date='9999-01-01')manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_no。
- 12、获取所有部门中当前(dept_emp.to_date = '9999-01-01')员工当前(salaries.to_date='9999-01-01')薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列。
- 13、从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
- 14、从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
- 15、查找employees表所有emp_no为奇数,且last_name不为Mary(注意大小写)的员工信息,并按照hire_date逆序排列
- 16、题目:从统计出当前各个title类型对应的员工当前(to_date=‘9999-01-01')薪水对应的平均工资。结果给出title以及平均工资avg。
- 17、获取当前(to_date='9999-01-01')薪水第二多的员工的emp_no以及其对应的薪水salary
- 18、查找所有员工的last_name和first_name以及对应的dept_name,也包括暂时没有分配部门的员工
- 19、查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
- 20、查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
11、获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager,如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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`));
方法1:使用内连接加上where多条件限制
解题思路:
- 确定连接字段 dept_no;
- 确定筛选条件 dept_manager.to_date = "9999-01-01"以及dept_emp.emp_no不在dept_manager中
select de.emp_no,dm.emp_no from
dept_emp as de
left join
dept_manager as dm
on de.dept_no=dm.dept_no
where dm.to_date="9999-01-01"
and de.emp_no not in (select dm.emp_no from dept_manager);
方法2:使用where多条件限制
select de.emp_no,dm.emp_no
from dept_emp as de,dept_manager as dm
where de.dept_no=dm.dept_no
and de.emp_no!=dm.emp_no
and dm.to_date="9999-01-01"
and de.to_date="9999-01-01";
12、获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息,给出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`));
解法:先找出各个部门最高的薪资,再找到各个员工的部门、薪资,联结查询后的两个表,这里筛选时需要两个条件,一个是部门,一个是最高薪资。
SELECT a.dept_no,b.emp_no,a.salary
FROM
(SELECT dept_no,MAX(salary) AS salary
FROM salaries
INNER JOIN dept_emp
ON dept_emp.emp_no=salaries.emp_no
WHERE dept_emp.to_date = '9999-01-01'
AND salaries.to_date='9999-01-01'
GROUP BY dept_no) AS a
##薪资最高的部门
INNER JOIN ##联合查询
(SELECT dept_no,dept_emp.emp_no,salary
FROM dept_emp
INNER JOIN salaries
ON dept_emp.emp_no=salaries.emp_no
WHERE dept_emp.to_date = '9999-01-01'
AND salaries.to_date='9999-01-01')AS b
##查询出员工部门、编号、薪资
ON a.salary=b.salary
AND a.dept_no=b.dept_no
ORDER BY dept_no;
13、从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);
解答:
select title,count(title) as t
from titles
group by title
having t>=2
此题应注意以下三点:
1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数
2、根据题意,输出每个title的个数为t,故用AS语句将COUNT(title)的值转换为t
3、由于WHERE后不可跟COUNT()函数,故用HAVING语句来限定t>=2的条件
14、从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算,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);
解答:
select title,count(distinct emp_no)as t
from titles group by title having t>=2;
15、查找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`));
解答:
SELECT *FROM employees WHERE emp_no % 2 = 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;
法一:使用%
select *
from employees
where emp_no%2 =1
and last_name!='Mary'
order by hire_date desc
补充:不相等有三种表示方式:<>、!=、IS NOT
法二:使用&
SELECT *
FROM employees
WHERE emp_no & 1
AND last_name<>'Mary'
ORDER BY hire_date DESC;
16、题目:从统计出当前各个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);
解:法1:
select title,avg(salary)
from titles as t
inner join
salaries as s
on s.emp_no=t.emp_no
and s.to_date='9999-01-01'
and t.to_date="9999-01-01"
group by title;
解法2:
select title,avg(salary)
from titles as t
inner join
salaries as s
on s.emp_no=t.emp_no
and s.to_date='9999-01-01'
and t.to_date="9999-01-01"
group by title;
17、获取当前(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`));
```python
答案:
select emp_no,salary from salaries
where salary=
(select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary desc limit 1,1);
18、查找所有员工的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`));
方法1:
select last_name,first_name,dept_name
from employees as e left join
(select emp_no,dept_name from dept_emp as de
left join departments
as d on de.dept_no=d.dept_no)as k
on e.emp_no=k.emp_no;
方法2:
select e.last_name,e.first_name,dept_name from employees as e
left join dept_emp as de on e.emp_no =de.emp_no
left join departments as d on d.dept_no=de.dept_no;
19、查找员工编号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`));
解法1:最高工资-最低工资
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;
解法2:
直接聚合函数,大减小
select max(salary)-min(salary) growth from salaries where emp_no = 10001;
解法3:
使用左连接的方法,在salaries表的基础上,利用to_date = from_date的约束,给每条数据后面再加上此次涨幅后的工资,然后将两次的工资做差,就知道此次涨薪多少了;也就是说做连接之后表中就记录了涨薪前和涨薪后的工资各为多少,然后做差求和即可。
select sum(s2.salary-s1.salary)
from salaries s1 , salaries s2
where s1.emp_no = 10001
and s1.to_date = s2.from_date
20、查找所有员工自入职以来的薪水涨幅情况,给出员工编号emp_no以及其对应的薪水涨幅growth,并按照growth进行升序
(注:可能有employees表和salaries表里存在记录的员工,有对应的员工编号和涨薪记录,但是已经离职了,离职的员工salaries表的最新的to_date!=‘9999-01-01’,这样的数据不显示在查找结果里面)
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`));
方法:
/*思路:用每位员工当前的薪水-入职的薪水=涨幅growth
每位员工当前的薪水:
SELECT emp_no,salary
FROM salaries
WHERE to_date!='9999-01-01'
每位员工入职时的薪水:
SELECT e.emp_no,s.salary
FROM employees AS e
JOIN
salaries AS s
ON e.emp_no = s.emp_no
WHERE e.hire_date = s.from_date*/
正式:
select b.emp_no,(b.salary-a.salary) as growth
from
(select e.emp_no,s.salary
from employees e
join salaries s
on e.emp_no=s.emp_no
and e.hire_date=s.from_date)a -- 入职工资表
inner join
(select e.emp_no,s.salary
from employees e
join salaries s
on e.emp_no=s.emp_no
where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth