牛客网——SQL21[感觉这题好难啊] &&SQL22

牛客网——SQL21记录

题目描述：

查找所有员工自入职以来的薪水涨幅情况，给出员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序
（注:可能有employees表和salaries表里存在记录的员工，有对应的员工编号和涨薪记录，但是已经离职了，离职的员工salaries表的最新的to_date!='9999-01-01'，这样的数据不显示在查找结果里面）
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`));
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`));

解答：
方法一：

select sCurrent.emp_no, (sCurrent.salary - sStart.salary) as growth from 
(select emp_no,salary  from salaries where salaries.to_date='9999-01-01'  )
as sCurrent,
(select e.emp_no,s.salary from employees e,salaries s where e.emp_no=s.emp_no
and e.hire_date = s.from_date  ) as sStart
where sCurrent.emp_no = sStart.emp_no 
order by growth;

思路：
分别找出 当前的工资 以及入职时工资 将这两张表进行连接 然后计算出growth

参考：https://www.cnblogs.com/kexiblog/p/10683481.html

方法二：

SELECT
	a.emp_no,
	( b.salary - c.salary ) AS growth 
FROM
	employees AS a
	INNER JOIN salaries AS b ON a.emp_no = b.emp_no 
	AND b.to_date = '9999-01-01'
	INNER JOIN salaries AS c ON a.emp_no = c.emp_no 
	AND a.hire_date = c.from_date 
ORDER BY
	growth ASC;

参考：https://blog.csdn.net/mathew_leung/article/details/83870728
【这题写了40多分钟。。】

---

牛客网——SQL22记录

题目描述：

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum
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 `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`));

解答：

select d.dept_no,d.dept_name,count(s.emp_no) as sum 
from departments d inner join dept_emp de on d.dept_no = de.dept_no
inner join salaries s on de.emp_no = s.emp_no 
group by d.dept_no;

注意：
聚合函数要与group by 一起使用！！！！！！

参考：https://www.cnblogs.com/jiguang321/p/12033605.html

方法二：【先查出一个员工 的涨幅次数 然后在部门里面进行分组 加】

select d.dept_no,de.dept_name,sum(empt.t)as sum
from dept_emp d,departments de,
    (select emp_no,count(emp_no) as t from salaries group by emp_no) as empt
where d.dept_no=de.dept_no and d.emp_no=empt.emp_no
group by d.dept_no

参考：https://www.cnblogs.com/kexiblog/p/10683586.html

---

牛客网——SQL23记录【没看咋懂，先写上再看】

题目描述：

对所有员工的当前(to_date='9999-01-01')薪水按照salary进行按照1-N的排名，相同salary并列且按照emp_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`));

解答：

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

分析：

1. 从两张相同的salaries表（分别为s1与s2）进行对比分析，先将两表限定条件设为to_date = ‘9999-01-01’，挑选出当前所有员工的薪水情况。
2. 本题的精髓在于 s1.salary <= s2.salary，意思是在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，比如当s1.salary=94409时，
   有3个s2.salary（分别为94692,94409,94409）大于等于它，但由于94409重复，利用COUNT(DISTINCT s2.salary)去重可得工资为94409的rank等于2。其余排名以此类推。
3. 千万不要忘了GROUP BY s1.emp_no，否则输出的记录只有一条（可能是第一条或者最后一条，根据不同的数据库而定），因为用了合计函数COUNT()
4. 最后先以 s1.salary 逆序排列，再以 s1.emp_no 顺序排列输出结果

参考：https://blog.csdn.net/ouzhuangzhuang/article/details/89640636