刷牛客SQL Part2

题目7：

https://www.nowcoder.com/practice/6d4a4cff1d58495182f536c548fee1ae?tpId=82&tqId=29759&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

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`));

题解：本题最好理解成，工资变动超过15次的员工号emp_no以及其对应的次数t

/*写法1：统计员工在表中出现了几次，就说明变动了几次*/
SELECT emp_no, COUNT(emp_no) AS t 
FROM salaries 
GROUP BY emp_no 
HAVING t > 15
/*写法2：统计不同的工资出现的次数*/
SELECT emp_no, COUNT(distinct salary) AS t 
FROM salaries 
GROUP BY emp_no 
HAVING t > 15

按照员工分组，组内记录大于15。
一点笔记：
WHERE语句在GROUP BY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUP BY语句之后；SQL会在分组之后计算HAVING语句。
小白疑惑之为什么用到了分组：使用聚合的时候，需要用分组，也就是GROUP BY，比如本题将所有相同的员工工资变动的次数浓缩到一行，聚在了一起，所以使用分组。（员工会出现多次，并在一起）

题目8：

https://www.nowcoder.com/practice/ae51e6d057c94f6d891735a48d1c2397?tpId=82&tqId=29760&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
找出所有员工当前(to_date='9999-01-01')具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

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：利用分组来进行去重*/
select salary 
from salaries 
where to_date='9999-01-01' 
group by salary 
order by salary desc
/*写法2：*/
select distinct(salary) 
from salaries
where to_date='9999-01-01'
order by salary desc;

一点笔记：在单表的时候使用distinct，多表的时候使用group by，虽然一般使用group by ，但还是要知道distinct的用法。显然数据量大时，使用group by是提高性能的。

题目9：

题目描述
获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary，当前表示to_date='9999-01-01'

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：*/
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_manager AS d, salaries AS s
WHERE d.emp_no = s.emp_no
AND d.to_date = s.to_date
AND d.to_date = '9999-01-01'
/*写法2：使用内联结*/
SELECT d.dept_no, d.emp_no, s.salary
FROM dept_manager AS d INNER JOIN salaries as s
ON d.emp_no = s.emp_no
AND d.to_date = s.to_date
AND d.to_date = '9999-01-01'

一点笔记：和联结相配合的是on而不是where。

题目10：

https://www.nowcoder.com/practice/32c53d06443346f4a2f2ca733c19660c?tpId=82&tqId=29762&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking
题目描述
获取所有非manager的员工emp_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 `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`));

题解：

/*写法1：使用In关键字。选出在employees但不在dept_manager中的emp_no记录*/
SELECT emp_no 
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager)
/*写法2：使用左联结，ab和bc的集合，本题要求取a。*/
SELECT emp_no FROM (SELECT * 
                    FROM employees LEFT JOIN dept_manager
                    ON employees.emp_no = dept_manager.emp_no)
WHERE dept_no IS NULL
//将空的经理表中空的去除，就是非经理的编号。where这句最核心
/*写法3：写法2的改进*/
SELECT employees.emp_no 
FROM employees LEFT JOIN dept_manager
ON employees.emp_no = dept_manager.emp_no
WHERE dept_no IS NULL

一点笔记：MySQL官方文档有说明，in关键字适合确定数量的情况，一般效率较低，不推荐使用。能用in关键字的语句都可以转化为使用join的语句，推荐使用join关键字。not in在实际使用中，因为not in会转化成多表连接，而且不使用索引，所以还是用left_join代替会好一点。

题目11：

https://www.nowcoder.com/practice/e50d92b8673a440ebdf3a517b5b37d62?tpId=82&tqId=29763&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
获取所有员工当前的manager，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。
结果第一列给出当前员工的emp_no,第二列给出其manager对应的manager_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 `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`));

题解：

/*写法1：*/
select e.emp_no ,m.emp_no as manager_no 
from dept_emp e,dept_manager m  
where e.dept_no=m.dept_no 
and e.to_date='9999-01-01' 
and m.to_date='9999-01-01' 
and e.emp_no<>m.emp_no;
//看到，emp_no这一列可以不改名，而manager中的必须改名（题目要求）
//第一种使用where。三个等于的限制条件，并且加一个不等于的限制条件<>或者!=都可以，建议前者
/*写法2：内连接，注意要有ON，这里的where替换成AND也可以。但必须要有no和no相等（主键）*/
SELECT dp.emp_no, dm.emp_no AS manager_no
FROM dept_emp dp INNER JOIN dept_manager dm
ON dp.dept_no = dm.dept_no
WHERE dm.to_date = '9999-01-01'
AND dp.to_date = '9999-01-01'
AND dp.emp_no <> dm.emp_no

题目12：

https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6?tpId=82&tqId=29764&tPage=1&rp=&ru=/ta/sql&qru=/ta/sql/question-ranking
题目描述
获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的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 `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`));

题解：

/*（如果同部门有多条同等最大salary，一起显示出来）*/
select r.dept_no,ss.emp_no,r.maxSalary 
from (
    select d.dept_no,max(s.salary)as maxSalary 
    from dept_emp d,salaries s
    where d.emp_no=s.emp_no
    and d.to_date='9999-01-01' 
    and s.to_date='9999-01-01'
    group by d.dept_no
    )as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary   //r表和ss表联系
and r.dept_no=dd.dept_no   //r表和dd表联系
and dd.emp_no=ss.emp_no //ss和dd主键相等联系
and ss.to_date='9999-01-01'
and dd.to_date='9999-01-01'
order by r.dept_no asc  //升序排序


--评论区的一些答案没有把GROUP BY 默认取非聚合的第一条记录考虑进去
/*解法2：比较容易看懂，来自小麦麦秋招加油 童鞋的解法*/
SELECT r1.dept_no, r1.emp_no, r1.salary 
FROM
    --创建r1表用于存放当前每个部门每个员工的薪水
    (
    SELECT d.dept_no, d.emp_no, s1.salary
    FROM dept_emp d, salaries s1
    WHERE d.to_date='9999-01-01' 
        AND s1.to_date='9999-01-01'
        AND d.emp_no = s1.emp_no)r1
JOIN  --创建r2表用于存放当前每个部门薪水的最大值
    (
    SELECT d.dept_no, MAX(s2.salary) as maxsalary
    FROM dept_emp d
    --为了避免GROUP BY默认取非聚合数据的第一条记录，先把salary排好序
    JOIN (SELECT * FROM salaries ORDER BY salary DESC)s2
    ON d.emp_no = s2.emp_no
    WHERE d.to_date='9999-01-01' 
        AND s2.to_date='9999-01-01'
    GROUP BY d.dept_no)r2
ON r1.salary = r2.maxsalary
    AND r1.dept_no = r2.dept_no
ORDER BY r2.dept_no

看讨论发现有人说有部分答案有问题，老实说，我没那个实力判断，并且也没有像很多大佬那些使用其他软件自己去测试。因此只能说，尝试理解大家解题的思路，具体的细节可能只能忽略，等以后头发更稀少的时候来看看。这里需要Mark一下。
别人的总结：使用group by子句时，select子句中只能有聚合键、聚合函数、常数。emp_no并不符合这个要求。

select
  d.dept_no,d.emp_no,max(s.salary) as salary
from dept_emp d,salaries s
where 
d.emp_no=s.emp_no and 
d.to_date=s.to_date
group by d.dept_no
貌似这个写法是错误的

我又来学习SQL啦文章被收录于专栏

巩固一下学习的SQL知识