牛客网SQL例题详解

最新推荐文章于 2023-04-14 16:13:22 发布

衣服架子

最新推荐文章于 2023-04-14 16:13:22 发布

阅读量714

点赞数 1

分类专栏： Mysql 文章标签： sql 面试 mysql 数据库

本文链接：https://blog.csdn.net/huijiaaa1/article/details/114452281

版权

Mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

例题1、查找薪水排名第二多的员工信息，不能使用order by完成（难度：较难）

本题目摘抄自牛客网：SQL18题
有一个员工表employees简况如下:
有一个薪水表salaries简况如下:

请你查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成，以上例子输出为:

看到此题，其实使用order by是可以很简单完成得。但是题目既然规定了，那么显而易见，为我们思维发散提供了空间。
说实话，看到题目，我刚开始也不知道怎么去写，只能先写出如下：

select 
    e.emp_no,s.salary,e.last_name,e.first_name 
from 
    employees e inner join salaries s on e.emp_no = s.emp_no  
    and s.to_date='9999-01-01' 
where salary = (

)

上述SQL语句也很好理解，通过内连接两个表，得到要查询的所有字段信息。但就是这个salary条件如何去做呢？
实在没有思路，看了下大佬的答案，然后经过一翻分析，算是对本题有些自己的了解。
首先，要获取工资第二高，那么这个如何完成？

我们可以让salary与自己进行内连接，别名分别为 s1 s2。
然后 group by s1.salary
再进行 count(distinct s2.salary) = 2 即可

即我们可以得到刚开始那里where后的查询条件语句：

select s1.salary
from salaries s1 join salaries s2 on s1.salary <= s2.salary
     and s1.to_date='9999-01-01' and s2.to_date ='9999-01-01' 
 group by s1.salary having count(distinct s2.salary) = 2

什么意思呢？哦… 对了，这个是答案的思路，那么我们如何理解上述思路呢？对啊，我刚开始看到这个语句也是一头雾水，为什么进行内连接？我们不妨做个简单的分析：
现在有一组数据：100 90 80
首先我们可以进行内连接得到并且标记出符合条件的数据：

s1.salary	s2.salary	s1.salary <=s2.salary
100	100	√
100	90
100	80
90	100	√
90	90	√
90	80
80	100	√
80	90	√
80	80	√

有了上面的数据，就可以很好理解了。
之后使用 s1.salary分组
使用s1.salary分组后，再使用 having进行组内筛选。为了防止每组可能会出现相同的数据，别忘了使用 distinct去重哦。即得到 :

group by s1.salary having count(distinct s2.salary) = 2

为什么 count(distinct s2.salary) = 2要等于2 呢？这句话的意思就是筛选出分组后刚好有两行数据的那一组，此时的 s1.salary也就是工资表第二高的工资。但为什么呢？
通过例举的数据以及自连接查询后的数据，以及限制条件s1.salary <=s2.salary，就可以得到 s1.salary越大，那么按他对应分组（group by）后的数据行数越少，如s1.salary 为最大值时，那么此时 s2.salary 去重后，该组对应的行数肯定是最少的！，且去重后仅仅只有一行！！为什么？看看判断条件，想想就知道了。

如此往复推断，可以得出，根据s1.salary分组后，然后判断对应去重后行数即就是当前s1.salary在整张工资表中的排名。
同时该sql语句也是通用的：

group by s1.salary having count(distinct s2.salary) = x

X可以取任意值，那么我们就可以得到工资表任意高的位置的值。
有没有想过，count(distinct s2.salary) 里面为什么要去重呢？如果工资表中有相同工资的项时，自连接后，并分组后，行数会相应的增多，不再能够表示该工资在整张工资表的排名。
最终得到SQL语句：

SELECT
	e.emp_no,
	s.salary,
	e.last_name,
	e.first_name 
FROM
	employees e
	INNER JOIN salaries s ON e.emp_no = s.emp_no 
	AND s.to_date = '9999-01-01' 
WHERE
	salary = (
	SELECT
		s1.salary 
	FROM
		salaries s1
		JOIN salaries s2 ON s1.salary <= s2.salary 
		AND s1.to_date = '9999-01-01' 
		AND s2.to_date = '9999-01-01' 
	GROUP BY
		s1.salary 
	HAVING
	count( DISTINCT s2.salary ) = 2 
	)

同时也学习到了还有一种稍微简单的方法：

 SELECT
	max( salary ) 
FROM
	salaries 
WHERE
	salary < ( SELECT max( salary ) FROM salaries WHERE to_date = '9999-01-01' ) 
	AND to_date = '9999-01-01'

即把里面的条件替换成如上sql，上面的sql非常好理解，先查出最大的工资，再从工资表查工资小于最大的，同时又是工资表最大的工资，不就是排名第二的工资吗。这么来看，这种方法比上面的简单多了，效率也快，但是了解第一种方法更能开拓下自己的思维~
遗憾的是，这道题两种解法我都没想出来，一些关键词理解的能力还有待于提高。

例题2.获取每个部门中当前员工薪水最高的相关信息(困难)

本题摘抄至牛客网sql12题

有一个员工表dept_emp简况如下:
在这里插入图片描述
有一个薪水表salaries简况如下:

获取所有部门中员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:

看完题目，很轻易地，就可以写出如下sql，但问题在于，如何完成括号里面的条件呢？

SELECT
	d.dept_no,
	d.emp_no,
	s.salary maxSalary 
FROM
	dept_emp d
	INNER JOIN salaries s ON d.emp_no = s.emp_no 
	AND s.to_date = '9999-01-01' 
	AND d.to_date = '9999-01-01' 
WHERE
	s.salary = (
	
	) 
GROUP BY
	d.dept_no 
ORDER BY
	d.dept_no ASC

我们一步一步来分析：
首先，员工表和工资表进行内连接，可是接下来如何得到各个部门员工工资最高的员工信息呢？
没思路…这可怎么查啊？要在组内分析，查询。怎么做呢？

哎？我们可以先查到当前员工部门里面最高的工资，然后代入上面的sql作为查询条件，不就可以了吗？是这么个道理，但是怎么关联在一起啊！
我们可以借用上面sql的dept_emp （即d.dept_no）与之对应不就欧克了吗？
对对对，茅塞顿开！

SELECT
		max( s2.salary ) 
	FROM
		dept_emp de2
		INNER JOIN salaries s2 ON de2.emp_no = s2.emp_no 
		AND de2.to_date = '9999-01-01' 
		AND s2.to_date = '9999-01-01' 
	WHERE
		de2.dept_no = d.dept_no 
	GROUP BY
		d.dept_no

看到 where后面的条件，什么瞬间就明白了。

总结：对于一些SQL查询，对于我还是有些生疏，应该通过不断地加强练习，再能进一步领略到SQL查询的魅力，增删改查不一定就是最简单的，每件事要做到极致都不容易，做题目还好，只管结果，可到现实生活中就不太行了，一旦数据量大了，数亿级的数据，就得考虑到SQL语句的优化了，如果连最基本的都搞不定，那么后续的就更不用提了。