牛客网 - 数据库实战 - 第二十题开始

嘤嘤嘤，感觉变难了，，，可是快完成三分之一了，

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

思路：建立两张表，分别存放所有员工当前的薪水和入职时的薪水，然后再用INNER JOIN连接sCurrent与sStart，最后限定在同一员工下用当前工资减去入职工资。

SELECT sCurrent.emp_no, (sCurrent.salary-sStart.salary) AS growth
FROM (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01') AS sCurrent
INNER JOIN (SELECT s.emp_no, s.salary FROM employees e LEFT JOIN salaries s ON e.emp_no = s.emp_no WHERE s.from_date = e.hire_date) AS sStart
ON sCurrent.emp_no = sStart.emp_no

ORDER BY growth

第二十一题：统计各个部门对应员工涨幅的次数总和，给出部门编码dept_no、部门名称dept_name以及次数sum

思路：从三张表中选出部门编号、部门名字，统计每组中工资表中from_date 的次数，按部门分组

SELECT dp.dept_no, dept_name, COUNT(s.from_date) AS sum FROM departments dp, dept_emp de, salaries s
WHERE de.emp_no=s.emp_no AND de.dept_no=dp.dept_no GROUP BY de.dept_no

 

第二十二题：对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_no升序排列

思路：自联结，当时学的时候还以为用不到呢。。。

重点： s1.salary <= s2.salary 是小于等于，我刚开始就写反了，计算表内比>=s1的个数即是s1的排序

SELECT s1.emp_no,s1.salary, COUNT(DISTINCT s2.salary) AS rank 
FROM salaries s1, salaries s2 
WHERE s1.salary <= s2.salary AND s1.to_date='9999-01-01' AND s2.to_date='9999-01-01' 
GROUP BY s1.emp_no ORDER BY rank, s1.emp_no;

第二十三题：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date='9999-01-01'

可怕，表越多就约容易迷糊，写了好久还是看了通过的代码讲解才写出来

本题主要思想是创建两张表（一张记录当前所有员工的工资，另一张只记录部门经理的工资）进行比较，具体思路如下：

1、先用INNER JOIN连接salaries和demp_emp，建立当前所有员工的工资记录s1

2、再用INNER JOIN连接salaries和demp_manager，建立当前所有员工的工资记录s2

3、最后用限制条件s1.dept_no = s2.dept_no AND s1.salary > s2.salary找出同一部门中工资比经理高的员工，并根据题意依次输出emp_no、manager_no、emp_salary、manager_salary

SELECT s1.emp_no as emp_no,s2.emp_no as manager_no, s1.salary as emp_salary, s2.salary as manager_salary FROM
(SELECT de.emp_no, s.salary, de.dept_no FROM dept_emp de INNER JOIN salaries s 
ON de.emp_no=s.emp_no AND de.to_date='9999-01-01' AND s.to_date='9999-01-01') AS s1,
(SELECT s.emp_no, s.salary, dm.dept_no FROM salaries s INNER JOIN dept_manager dm
ON dm.emp_no=s.emp_no AND s.to_date='9999-01-01') AS s2
WHERE s1.dept_no = s2.dept_no AND s1.salary > s2.salary

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

我的问题出现在这里：GROUP BY de.dept_no, t.title ，我知道要对这两个同时排序，可是不知道怎么写，中间没加逗号一直报错，这个答案是按dept_no排过序输出的，最好用ORDER BY 排序一下，GROUP BY不会排序

SELECT de.dept_no, dm.dept_name, t.title, COUNT(title) as count FROM departments dm, dept_emp de, titles t
WHERE de.emp_no=t.emp_no AND de.dept_no=dm.dept_no AND t.to_date='9999-01-01' AND de.to_date='9999-01-01' GROUP BY de.dept_no, t.title 
ORDER BY de.dept_no

不过我看了一下我的这个居然占内存比答案里的小，按它的写一下

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

 

第二十五题

给出每个员工每年薪水涨幅超过5000的员工编号emp_no、薪水变更开始日期from_date以及薪水涨幅值salary_growth，并按照salary_growth逆序排列

难点主要在于每年这两个字

假设s1是涨薪水前的表，s2是涨薪水后的表，因为每个员工涨薪水的时间不全固定，有可能一年涨两次，有可能两年涨一次，所以每年薪水的涨幅，应该理解为两条薪水记录的from_date相同或to_date相同。

SELECT s2.emp_no, s2.from_date, (s2.salary-s1.salary) as salary_growth FROM salaries s1, salaries s2 
ON s1.emp_no=s2.emp_no AND salary_growth > 5000
AND (strftime('%Y',s2.to_date)-strftime('%Y',s1.to_date)=1 
     OR strftime("%Y",s2.from_date) - strftime("%Y",s1.from_date) = 1)
ORDER BY salary_growth DESC

 

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

思路：本题可以使用通配符，用来匹配值的一部分的特殊字符，另外Group BY和count都是用的id，我最开始想简单了都用了名字，但是名字有可能有重复的，从而出错

其实我最开始想到了全文本搜索，但是没有FULLTEXT的设置

题目有错,应该是>=2

SELECT c.name, count(f.film_id) as count FROM film f,category c,film_category fc
WHERE f.description LIKE '%robot%' AND f.film_id=fc.film_id AND fc.category_id=c.category_id
Group BY c.category_id HAVING count>=2