SQL实战练习【2】

六、dept_emp和dept_manager表如下：

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

 获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_no。

本题应注意以下三点：

1、用INNER JOIN连接两张表，因为要输出自己的经理，得知自己与经理的部门要相同，故有限制条件 de.dept_no = dm.dept_no

2、再用WHERE限制当前员工与当前经理的条件，即 dm.to_date 等于 '9999-01-01' 、de.to_date 等于 '9999-01-01' 、 de.emp_no 不等于dm.emp_no

3、为了增强代码可读性，将dept_emp用别名de代替，dept_manager用dm代替，最后根据题意将de.emp_no用别名manager_no代替后输出

1 2 3 4

SELECT de.emp_no, dm.emp_no AS manager_no  FROM dept_emp AS de INNER JOIN dept_manager AS dm ON de.dept_no = dm.dept_no  WHERE dm.to_date =  '9999-01-01' AND de.to_date =  '9999-01-01' AND de.emp_no <> dm.emp_no

七、 dept_emp和salaries表如下：

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

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

此题思路如下：

1、先用INNER JOIN连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no；

2、选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制，因为此表中每条最新记录的 to_date 都用 9999-01-01 表示；

3、用GROUP BY d.dept_no将每个部门分为一组，用MAX()函数选取每组中工资最高者；

4、将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

1 2 3 4 5

SELECT d.dept_no, s.emp_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no

------------------------------------------分割线：若存在多条最大记录----------------------------------------

有同学提出疑问， 如果存在多条最大记录怎么办？而 MAX 函数根据不同数据库只选择最前一条或最后一条最大记录，其余记录均被忽略。此时解法如下：

1、创建两张表，一张为maxsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；

2、限定条件为两张表的 dept_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息了；

3、最后记得根据 currentsalary.dept_no 升序排列，输出与参考答案相同的记录表。

4、以下代码虽然很长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX 的特性限制，运用比较的方法选出多个相同的最大值。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT currentsalary.dept_no, currentsalary.emp_no, currentsalary.salary AS salary FROM  //创建maxsalary表用于存放当前每个部门薪水的最大值 (SELECT d.dept_no, MAX(s.salary) AS salary FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' GROUP BY d.dept_no) AS maxsalary,  //创建currentsalary表用于存放当前每个部门所有员工的编号和薪水 (SELECT d.dept_no, s.emp_no, s.salary  FROM salaries AS s INNER JOIN dept_emp As d ON d.emp_no = s.emp_no  WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01' ) AS currentsalary //限定条件为两表的dept_no和salary均相等 WHERE currentsalary.dept_no = maxsalary.dept_no AND currentsalary.salary = maxsalary.salary //最后以currentsalary.dept_no排序输出符合要求的记录表 ORDER BY currentsalary.dept_no

八、 titles表如下：

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

    此题应注意以下三点：

   1、用COUNT()函数和GROUP BY语句可以统计同一title值的记录条数

   2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(title)的值转换为t

   3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

1 2	SELECT title, COUNT(title) AS t FROM titles GROUP BY title HAVING t >= 2

2. 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略。

此题应注意以下三点：

1、先用GROUP BY title将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

2、根据题意，输出每个title的个数为t，故用AS语句将COUNT(DISTINCT emp_no)的值转换为t

3、由于WHERE后不可跟COUNT()函数，故用HAVING语句来限定t>=2的条件

1 2	SELECT title, COUNT(DISTINCT emp_no) AS t FROM titles GROUP BY title HAVING t >= 2

九、 employees 表如下：

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

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

三点需要注意：

1、员工号为奇数，则emp_no取余应为1

2、last_name不为Mary，用‘！=’表示

3.根据hire_date逆序排列，用desc

select * from employees
where emp_no % 2 = 1
and last_name != 'Mary'
order by hire_date desc

十、 salaries和titles 表如下：

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

统计出当前各个title类型对应的员工当前薪水对应的平均工资。结果给出title以及平均工资avg。

首先本题有毒，思路如下：

1、先算出当前员工的当前工资表，即由每个emp_no的to_date为最大时的记录构成的表

2、再将此表与titles连接，限定条件为emp_no相等且to_date相等，即得当前员工的工资表

3、最后以title分组，利用AVG()函数计算每个title下的平均工资

1 2 3 4 5

SELECT t.title AS title, AVG(s.salary) AS avg  FROM titles AS t  INNER JOIN (SELECT * FROM salaries GROUP BY emp_no HAVING to_date = MAX(to_date)) AS s ON s.emp_no = t.emp_no AND s.to_date = t.to_date  GROUP BY t.title

无奈以上代码无法通过，必须再加一条限定s.to_date = '9999-01-01'，则意义变为【当 前各个title类型对应的经理当前薪水对应的平均工资】，并且查看测试代码发现emp_no=10008的员工最新工资记录的to_date不等于'9999-01-01'。  所以不是题错了就是OJ系统错了。

1 2 3 4 5

SELECT t.title AS title, AVG(s.salary) AS avg  FROM titles AS t  INNER JOIN (SELECT * FROM salaries GROUP BY emp_no HAVING to_date = MAX(to_date)) AS s ON s.emp_no = t.emp_no AND s.to_date = t.to_date AND s.to_date = '9999-01-01' GROUP BY t.title