刷牛客SQL Part5

题目25：

获取员工其当前的薪水比其manager当前薪水还高的相关信息
题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01',
结果第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_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 `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`));

题解：

三个表，雇员，经理，薪水。
雇员和薪水，经理和薪水，雇员和经理联系起来，最主要的联结是薪水表和雇员、薪水表和经理这两次联结
//解法1：薪水和雇员，薪水和经理作内联接，然后用from并列两个表，从而将雇员和经理能联系起来。
SELECT sem.emp_no AS emp_no, sdm.emp_no AS manager_no, 
       sem.salary AS emp_salary, sdm.salary AS manager_salary
FROM (SELECT s.salary, s.emp_no, de.dept_no 
      FROM salaries s INNER JOIN dept_emp de
      ON s.emp_no = de.emp_no 
      AND s.to_date = '9999-01-01' ) AS sem, 
     (SELECT s.salary, s.emp_no, dm.dept_no 
      FROM salaries s INNER JOIN dept_manager dm
      ON s.emp_no = dm.emp_no 
      AND s.to_date = '9999-01-01' ) AS sdm
WHERE sem.dept_no = sdm.dept_no AND sem.salary > sdm.salary

//解法2：三个表直接做内联结（实际上需要用4个表即薪水表用2次）
select de.emp_no ,(dm.emp_no) as manager_no , (s1.salary) as emp_salary ,
       (s2.salary) as manager_salary 
from salaries as s1, salaries as s2 
inner join dept_emp as de 
    on de.emp_no =s1.emp_no
inner join dept_manager as dm 
    on dm.emp_no =s2.emp_no
where de.dept_no=dm.dept_no 
and s1.to_date='9999-01-01' 
and s2.to_date='9999-01-01' 
and s1.salary>s2.salary

题目26：

汇总各个部门当前员工的title类型的分配数目
题目描述
汇总各个部门当前员工的title类型的分配数目，结果给出部门编号dept_no、dept_name、其当前员工所有的title以及该类型title对应的数目count

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 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 de.dept_no, dp.dept_name, t.title, COUNT(t.title) AS count
FROM titles AS t 
INNER JOIN dept_emp AS de 
    ON t.emp_no = de.emp_no 
    AND de.to_date = '9999-01-01' 
    AND t.to_date = '9999-01-01'
INNER JOIN departments AS dp 
    ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title
//写法2：不用联结，用where
select b.dept_no,b.dept_name,t.title,count(*) count
from titles t,dept_emp a,departments b
where a.dept_no=b.dept_no 
and t.emp_no=a.emp_no
and a.to_date='9999-01-01' 
and t.to_date='9999-01-01'
group by b.dept_no,t.title;

题目27

给出每个员工每年薪水涨幅超过5000的员工编号emp_no.....
菜鸟顶不住哇，我没看懂这道题，准备发量变少了再来，大佬们请移步本题的讨论区。

题目28 由于给出的是图表，所以就不一一截图了

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部
题解：

三个表
SELECT c.name, COUNT(fc.film_id) FROM
 (select category_id, COUNT(film_id) AS category_num 
  FROM film_category  
  GROUP BY category_id 
  HAVING count(film_id)>=5) AS cc,
 film AS f, film_category AS fc, category AS c
WHERE  f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id = cc.category_id

一点笔记：
1、找到对应电影数量>=5的所有分类，建立成虚表cc：(select category_id, count(film_id) as category_num from film_category group by category_id having count(film_id)>=5) as cc
2、设定限制条件 f.description like '%robot%'
3、在表cc、f、fc、c中查找包括robot的电影对应的分类名称和对应的电影数目。

题目29：

使用join查询方式找出没有分类的电影id以及名称
题解：

1、用 LEFT JOIN 连接 film 和 film_category，限定条件为 f.film_id = fc.film_id，
即连接电影 id 和电影分类 id，如果电影没有分类，则电影分类 id 显示 null
2、再用 WHERE 来限定条件 fc.category_id IS NULL 选出没分类的电影
虽然给了三个表，但是只用两个
SELECT f.film_id, f.title 
FROM film f LEFT JOIN film_category fc
ON f.film_id = fc.film_id 
WHERE fc.category_id IS NULL

一点笔记：
注意：最后一句若写成 ON f.film_id = fc.film_id AND fc.category_id IS NULL，则意义变成左连接两表 film_id 相同的记录，且 film_category 原表中的 fc.category 的值为 null。显然，原表中的 fc.category 的值恒不为 null，因此（f.film_id = fc.film_id AND fc.category_id IS NULL）恒为 FALSE，左连接后则只会显示 film 表的数据，而 film_category 表的数据全显示为 null

在使用left join时，and和where条件的区别如下：
1、 and条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。和右边的表关联不上，右边表的字段显示null；
2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

题目30：

使用子查询的方式找出属于Action分类的所有电影对应的title,description
题解：

//非子查询 将三个表做内连接
select f.title,f.description
from film as f 
inner join film_category as fc 
on f.film_id = fc.film_id            
inner join category as c 
on c.category_id = fc.category_id
where c.name = 'Action';
//非子查询写法2
SELECT f.title, f.description 
FROM film f, film_category fc, category c
WHERE f.film_id = fc.film_id 
AND fc.category_id = c.category_id 
AND c.name = 'Action'
//子查询写法

select f.title,f.description from film as f
where f.film_id in (select fc.film_id from film_category as fc
               where fc.category_id in (select c.category_id from category as c
                                        where c.name = 'Action'));

一点总结：虽然题目要求子查询，但是在非考试的状态下，能做出来即可。