MySQL 实例练习---day03

2021/8/25 每日一句
The crisis of today is the joke of tommorw.
今天的危机将成为明天的笑谈。——H. G. Wells

牛客网 SQL28 题有小伙伴会做吗？裂开了

SQL 25

获取员工其当前的薪水比其manager当前薪水还高的相关信息

第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

解题分析：
第一步：找到是管理者的员工,并得到其工资表

SELECT b.emp_no AS manager_no, s.salary FROM dept_emp a, dept_manager b, salaries s
WHERE a.emp_no = b.emp_no AND a.dept_no = b.dept_no AND b.emp_no = s.emp_no

第二步：找到在同一部门，但是非管理者的员工，并得到其工资表

SELECT a.emp_no AS emp_no, s.salary FROM dept_emp a, dept_manager b, salaries s 
WHERE a.emp_no != b.emp_no AND a.dept_no = b.dept_no AND a.emp_no = s.emp_no

第三步：将两张表联结起来查询，通过部门相同且普通员工工资大于经理工资作为条件判断，得出正确答案

SELECT t2.emp_no, t1.manager_no, t2.salary AS emp_salary, t1.salary AS manager_salary FROM 
(SELECT a.dept_no, b.emp_no AS manager_no, s.salary FROM dept_emp a, dept_manager b, salaries s
WHERE a.emp_no = b.emp_no AND a.dept_no = b.dept_no AND b.emp_no = s.emp_no
) t1
INNER JOIN
(SELECT a.dept_no, a.emp_no AS emp_no, s.salary FROM dept_emp a, dept_manager b, salaries s 
WHERE a.emp_no != b.emp_no AND a.dept_no = b.dept_no AND a.emp_no = s.emp_no
) t2
ON t2.salary > t1.salary and t2.dept_no = t1.dept_no

SQL 26

汇总各个部门当前员工的title类型的分配数目

即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序

SELECT b.dept_no, b.dept_name, title, COUNT(*)
FROM dept_emp a, titles t
JOIN departments b
where a.emp_no = t.emp_no AND a.dept_no = b.dept_no
GROUP BY  b.dept_no, t.title
ORDER BY b.dept_no ASC

注意：GROUP BY b.dept_no, t.title分组统计的字段顺序不要弄错了，不然用例不能全部通过

SQL 28

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

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

1、在 film 表中找到描述信息(film.description)中包含robot的电影的 film_id ，以及电影数目

SELECT film_id, COUNT(film_id) FROM film
WHERE `description` LIKE '%robot%'
GROUP BY film_id

2、从 film_category 表中找到对应电影数量>=5的所有分类

SELECT category_id, COUNT(film_id) AS category_num 
FROM film_category  
GROUP BY category_id 
HAVING COUNT(film_id)>=5

3、利用三张表之间的关系找出该电影对应的分类名称

SELECT c.name, t1.film_num FROM film_category fc
JOIN 
(
SELECT film_id, COUNT(film_id) AS film_num FROM film
WHERE `description` LIKE '%robot%'
GROUP BY film_id
) t1
JOIN category c
JOIN
(SELECT category_id, COUNT(film_id) AS category_num FROM
     film_category  GROUP BY category_id HAVING COUNT(film_id)>=5) t2
WHERE fc.film_id = t1.film_id AND fc.category_id = c.category_id AND t2.category_id = c.category_id

这道题自己思考了很长时间，也参考了牛友们的答案，感觉哪里还是没有搞明白。我怀疑题目是不是条件不明确，还是自己写的 SQL 语句有问题，头大了。有没有哪位小伙伴可以解答一下，三克油！