牛客网mysql练习21~30

牛客网mysql练习21~30

21.请你查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

分析：发现实例还会降薪 所以不能用max-min来实现…
应该用最大fromdate时间的工资-入职时间的工资且最大todate的时间必须是9999年保证员工没有离职。

SELECT ss.EMP_NO , (ss.SALARY - salaryin.sin) AS growth
FROM salaries ss
LEFT JOIN (
SELECT e.EMP_NO , s.SALARY AS "sin" 
FROM employees e, salaries s
WHERE e.HIRE_DATE = s.FROM_DATE) AS salaryin
ON ss.EMP_NO = salaryin.EMP_NO
WHERE ss.TO_DATE ="9999-01-01"
ORDER BY GROWTH ASC ;

22.请你统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

方法1：简单粗暴 三表连接

SELECT d.dept_no, d.dept_name, COUNT(salary) 'sum' 
FROM departments d,dept_emp de, salaries s
WHERE d.dept_no = de.dept_no
AND de.emp_no = s.emp_no
GROUP BY d.dept_no;

方法2：估计想得复杂了 应该不用嵌套这么多 有空再看

SELECT DEPT_NO, DEPT_NAME , COUNT(DEPT_NAME) AS "sum"
FROM 
(SELECT d.DEPT_NO ,d.DEPT_NAME
FROM departments d 
RIGHT JOIN (
SELECT s.EMP_NO ,de.DEPT_NO 
FROM salaries s 
JOIN dept_emp de 
ON s.EMP_NO =de.EMP_NO ) AS sde
ON d.DEPT_NO = sde.DEPT_NO) AS sum_table
GROUP BY DEPT_NO
ORDER BY DEPT_NO;

注意：这里GROUP BY聚合dept_no,而dept_name看上去不是聚合列(因此似乎不能放进SELECT语句内)，但是事实上，由于dept_no是主键，所以一个dept_no只会对应一个dept_name，所以可以认为dept_name是可聚合的，所以可以放进去。特别要注意的是，如果dept_no不是主键，而是联合主键之一或者别的情况，总之dept_no不能唯一确定dept_name，那么即使在表中看上去dept_no和dept_name是一一对应的，也不能将dept_name放进SELECT中，强行放进去，在mysql中也会报错(当然在题库中不会报错)。https://www.nowcoder.com/profile/209804443

23.对所有员工的薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列(开窗函数 没学 回头看)

24.获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

SELECT de.DEPT_NO ,e.EMP_NO,s.SALARY 
FROM employees e, dept_emp de,salaries s
WHERE e.EMP_NO = de.EMP_NO 
AND de.EMP_NO = s.EMP_NO
AND e.EMP_NO NOT IN 
(SELECT EMP_NO 
FROM dept_manager);

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

分析：这道题适合分开来看
员工表和工资表连接 得到所有员工的工资

SELECT de.EMP_NO,de.DEPT_NO , s1.SALARY 
FROM dept_emp de, salaries s1
WHERE de.EMP_NO = s1.EMP_NO

经理表和工资表连接 得到经理的工资

SELECT dm.EMP_NO ,dm.DEPT_NO ,s2.SALARY 
FROM dept_manager dm, salaries s2
WHERE dm.EMP_NO = s2.EMP_NO

最后把得到的连个子表连接 用条件限制 就可以查询出来

SELECT t1.emp_no AS emp_no , t2.emp_no AS manager_no ,
t1.salary AS emp_salary, t2.salary AS manager_salary
FROM 
(SELECT de.EMP_NO,de.DEPT_NO , s1.SALARY 
FROM dept_emp de, salaries s1
WHERE de.EMP_NO = s1.EMP_NO) AS t1 
LEFT JOIN 
(SELECT dm.EMP_NO ,dm.DEPT_NO ,s2.SALARY 
FROM dept_manager dm, salaries s2
WHERE dm.EMP_NO = s2.EMP_NO) AS t2
ON t1.dept_no = t2.dept_no
WHERE t1.emp_no!=t2.emp_no
AND t1.salary > t2.salary;

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

分析：这道题的重点是用group by 对多个字段进行分组。虽然SELECT里面的子查询只出现了一个列：count()，但是子查询会返回多个值，所以仍然不可行(必须只返回单值)。所以，需要用group by A,B ，AB为键值对来组成一组。

SELECT  dd.DEPT_NO ,dd.DEPT_NAME , t.title, COUNT(t.title) AS count
FROM titles t,
(SELECT de.EMP_NO ,de.DEPT_NO ,d.DEPT_NAME 
FROM dept_emp de, departments d
WHERE de.DEPT_NO = d.DEPT_NO ) AS dd
WHERE t.emp_no = dd.emp_no
GROUP BY dd.dept_no,t.title
ORDER BY dd.dept_no;

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

SELECT  c.NAME , COUNT(c.NAME) 
FROM category c, film f,film_category fc
WHERE fc.CATEGORY_ID =c.CATEGORY_ID 
AND fc.FILM_ID =f.FILM_ID
AND f.DESCRIPTION LIKE '%Robot%'
AND c.NAME IN 
(SELECT  c2.NAME 
FROM category c2, film f2,film_category fc2
WHERE fc2.CATEGORY_ID =c2.CATEGORY_ID 
AND fc2.FILM_ID =f2.FILM_ID
GROUP BY c2.CATEGORY_ID 
HAVING COUNT(fc2.CATEGORY_ID)>=5)
GROUP BY c.CATEGORY_ID ;

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

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 ;

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

SELECT f.TITLE ,f.DESCRIPTION 
FROM film f
WHERE f.FILM_ID IN (
SELECT fc.FILM_ID 
FROM film_category fc
WHERE fc.CATEGORY_ID = (
SELECT c1.CATEGORY_ID 
FROM category c1
WHERE c1.NAME = 'action'));