SQL面试条件筛选、DISTINCT、并列查询

于 2020-09-13 15:55:47 发布
阅读量2k 收藏 3
点赞数 2
分类专栏： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/xfxlesson/article/details/108560384
版权
本文介绍了SQL面试中涉及的多个查询技巧，包括查找最晚入职员工、薪资涨幅超过15次的员工、部门经理当前薪水等。通过案例解析了INNER JOIN、LIMIT、OFFSET、DISTINCT等关键操作的使用。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、目录

题目链接：
https://www.nowcoder.com/ta/sql

题目知识点
1条件筛选和MAX()函数
2LIMIT 和 OFFSET 的用法
3INNER JOIN
4INNER JOIN
5LEFT JOIN
6INNER JOIN/并列查询 ORDER BY
7COUNT()函数、GROUP BY 、HAVING
8DISTINCT（GROUP BY去重的用法）
9INNER JOIN/ 并列查询
10LEFT JOIN、NOT IN、 IS NULL

二、题目

1. 查找最晚入职员工的所有信息
  • 题目描述
查找最晚入职员工的所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为--,mysql为comment)
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`));

  • 思路
    筛选出符合最晚入职要求的员工编号的全部信息

  • 代码

select * from employees
where hire_date in (select max(hire_date) from employees)
2. 查找入职员工时间排名倒数第三的员工所有信息
  • 题目描述
查找入职员工时间排名倒数第三的员工所有信息，为了减轻入门难度，目前所有的数据里员工入职的日期都不是同一天
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`));

  • 思路
    利用LIMIT和OFFSET限定记录范围

  • 代码

select * from employees
order by hire_date DESC LIMIT 1 OFFSET 2
3. 查找当前薪水详情以及部门编号dept_no
  • 题目描述
查找各个部门当前(dept_manager.to_date='9999-01-01')领导当前(salaries.to_date='9999-01-01')薪水详情以及其对应部门编号dept_no
(注:请以salaries表为主表进行查询，输出结果以salaries.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`));
CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL, -- '部门编号'
`emp_no` int(11) NOT NULL, --  '员工编号'
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

  • 思路
    1.两张表通过emp_no内连接
    2.设置筛选条件dept_manager.to_date=‘9999-01-01’ and salaries.to_date=‘9999-01-01’

  • 代码

select s.*,dm.dept_no
from salaries s inner join dept_manager dm ON s.emp_no = dm.emp_no 
where s.to_date='9999-01-01' and dm.to_date='9999-01-01'
order by s.emp_no
  • 参考资料
    https://www.runoob.com/sqlite/sqlite-joins.html
    1.交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配。如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。
    2.内连接（INNER JOIN）（默认join）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
    3.外连接 - OUTER JOIN 以一个表做基准，匹配到的值加入主表，外连接的列使用 NULL 值，将它们附加到结果表中。
4.查找所有已经分配部门的员工的last_name和first_name
  • 题目描述
查找所有已经分配部门的员工的last_name和first_name以及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 `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.筛选已分配部门的员工
    2.将有部门的员工表dept_emp作为主表连接
  • 代码
select e.last_name,e.first_name,de.dept_no from 
dept_emp de join employees e on de.emp_no=e.emp_no
5. 查找所有员工的last_name和first_name以及对应部门编号dept_no
  • 题目描述
查找所有员工的last_name和first_name以及对应部门编号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 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.同于4题，但是需要展示没有分配部门的员工，使用employees 做为主表左连接

  • 代码

select e.last_name,e.first_name,de.dept_no 
from employees e
left join dept_emp de on e.emp_no=de.emp_no
6. 查找所有员工入职时候的薪水情况
  • 题目描述
查找所有员工入职时候的薪水情况，给出emp_no以及salary， 并按照emp_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`));
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.用emp_no关键字连接两张表；
    2.对入职时间做限制，employees表的hire_date = salaries表的from_date 限制；
    3.按照emp_no逆序。

  • 代码
    方法一：INNER JOIN连接两张表

SELECT s.emp_no, s.salary
from employees e inner join salaries s on 
e.emp_no=s.emp_no and  s.from_date=e.hire_date
order by s.emp_no desc

方法二：并列查询

SELECT s.emp_no, s.salary
from employees e,salaries s
where e.emp_no=s.emp_no and e.hire_date=s.from_date
order by s.emp_no desc
  • 参考资料
    https://www.nowcoder.com/questionTerminal/23142e7a23e4480781a3b978b5e0f33a
    内连接是取左右两张表的交集形成一个新表，用FROM并列两张表后仍然还是两张表。如果还要对新表进行操作则要用内连接。从效率上看应该FROM并列查询比较快，因为不用形成新表。本题从效果上看两个方法没区别。
7. 查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
  • 题目描述
查找薪水变动超过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`));

  • 思路
    1.通过group by分组后对组内员工薪资变动统计，并限制次数having t>=15;
    2.count() 结果命名为t.

  • 代码

select emp_no,count(salary) as t 
from salaries
group by emp_no 
having t>=15
  • 参考资料
    https://www.iteye.com/blog/keep-240665
    HAVING 和 WHERE 的区别
    1.在select语句中可以使用groupby子句将行划分成较小的组，使用聚组函数返回每一个组的汇总信息，可以使用having子句限制返回的结果集。
    2.where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。
8. 找出所有员工当前具体的薪水salary情况
  • 题目描述
找出所有员工当前(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.限定to_date=‘9999-01-01’，并且order by逆序
    2.使用distinct去重

  • 代码

select distinct salary from salaries
where to_date='9999-01-01'
order by salary desc

SELECT salary FROM salaries 
WHERE to_date = '9999-01-01'
GROUP BY salary
ORDER BY salary DESC
9.获取所有部门当前manager的当前薪水情况
  • 题目描述
获取所有部门当前(dept_manager.to_date='9999-01-01')manager的当前(salaries.to_date='9999-01-01')薪水情况，给出dept_no, emp_no以及salary(请注意，同一个人可能有多条薪水情况记录)
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.通过emp_no连接dept_manager和salaries两张表
    2.限定"当前"条件

  • 代码
    方法一：join

select dm.dept_no,dm.emp_no,s.salary 
from salaries s join dept_manager dm
on s.emp_no=dm.emp_no and dm.to_date='9999-01-01' and s.to_date='9999-01-01'

方法二：并列查询

select dm.dept_no,dm.emp_no,s.salary 
from dept_manager dm,salaries s 
where  dm.emp_no = s.emp_no AND dm.to_date = '9999-01-01' AND s.to_date = '9999-01-01'
  • 参考资料
    参考同题6
10. 获取所有非manager的员工emp_no
  • 题目描述
获取所有非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.筛选出dept_manager中没有的emp_no

  • 代码
    方法一：NOT IN

select e.emp_no
from employees e 
where e.emp_no not in (select dm.emp_no from dept_manager dm)

方法二：IS NULL

SELECT e.emp_no
FROM employees AS e LEFT JOIN dept_manager AS dm ON e.emp_no = dm.emp_no
WHERE dm.dept_no IS NULL
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值