SQL练习题与解析

author：Yuhao Wang

last_update_time：2020-02-25

一、练习说明

对于大多数小伙伴来说，最主要的是查询语句的使用。想要真正的掌握SQL，最好的方法就是不断的刷题，逐步强化锻炼你对于SQL的结构化思维，这一点非常重要。

刷题的方式有两种：

第一种：在数据库中建好数据表并导入数据，然后在查询环境中书写SQL提交查询。这种方式更适合初学者，因为每次提交SQL语句后，编译器都会对你的SQL进行解析，如果书写错误会及时提示，告诉你错在什么地方以便于你进一步调试。

第二种：直接看表结构，然后直接在本子上或者编辑工具中写SQL，在你确认无误后直接对照答案检查。这种方式直接跳过了机器检验，更考验你对SQL的熟悉程度。当你有一定的基础后，我更推荐这种方式。

最后提两点大家在书写SQL时的小建议，养成良好的习惯也至关重要：

写注释：对于初学者而言，写注释可以帮你理清思路；对于有一定基础的小伙伴而言，注释可以快速帮你理解复杂SQL的作用，极大提升工作效率
注意缩进：对于复杂SQL来说，好的缩进层次可以极大的提升阅读体验，也可以令SQL看起来简洁有序。

二、在线练习网站推荐

这里推荐几个在线SQL学习网站(排名不分先后，点击名称即可跳转)：

sqlzoo：这个网站包括了SQL学习的教程和参考资料，支持多种SQL搜索引擎且支持多过语言，题目由易到难。
SQLBolt： SQLBolt 是一个适合小白学习 SQL 的网站，这里由浅及深的介绍了 SQL 的知识，每一个章节是一组相关的 SQL 知识点，且配备着相应的练习，唯一的缺点是英文的。
xuesql.cn：相当于 SQLBolt 的中文版，包含视频、手册、在线练习。
leetcode：不仅可用于刷算法题，也有关于SQL的题目，提交答案后在线测评，也会让你很直观的看到自己所写的 SQL 的运行速度等。设有讨论区，可以去借鉴别人优化好的 SQL
牛客网：与leetcode相似，可以在线提交与测评。

三、练习数据准备

数据导入

本文题目大部分出牛客网数据库专题，大家可以进入网站自行练习，也可以在自己的电脑上导入数据集，这里需安装MySQL客户端与Navicat(非必须)。牛客网的搜索引擎为SQLite，少部分语法与MySQL不兼容，个人更倾向于离线本地学习，这样可以顺便对SQL中的建表也有一定的了解。测试数据集我放在网盘，提取码：0gad，大家自取。导入数据的方式可以通过在Navicate中右击数据库名后点击运行SQL文件，可以以直接运行脚本内的SQL，在此不再赘述，另外本文的题目主要是查询类题目，如果大家想了解对于数据库、数据表、数据列的操作，可以看我的另外一篇文章。

数据结构

这里简要给大家列举一下会用到的数据库表结构，方便大家做题的时候对照查看。

`employees` 员工表

该表存放员工的基本信息

列名	字段类型	是否为空	含义
`emp_no`	int(11)	否	员工ID，主键
`birth_date`	date	否	生日
`first_name`	varchar(14)	否	姓
`last_name`	varchar(16)	否	名
`gender`	char(1)	否	性别
`hire_date`	date	否	雇佣日期

select * from employees  where hire_date =(select hire_date  from employees  order by hire_date desc limit 1);
select * 
from employees 
where hire_date = (select max(hire_date) from employees);

`dept_manager` 部门领导表

该表存放每个部门的领导信息，使用dept_no与emp_no作为复合主键

列名	字段类型	是否为空	含义
`dept_no`	char(4)	否	部门ID
`emp_no`	int(11)	否	员工ID
`from_date`	date	否	开始日期
`to_date`	date	否	结束日期

提问：为何要使用使用dept_no与emp_no作为复合主键？

举例说明：雇员A在2016年至2018年为部门A的领导，在2018-2019年为部门B的领导，2020年至今为部门C的领导，则雇员A在dept_manager 中存在3条记录，且最后一条记录(2020年至今)的to_date为’9999-01-01’，因此要使用复合主键才能唯一确定一条记录。

扩展：这种数据模型在数据仓库的设计过程中被称为拉链表。既能满足反应数据的历史状态，又可以最大程度的节省存储

`salaries` 薪水表

该表存放所有雇员的薪水信息。使用emp_no与from_date作为复合主键，原因与上表相同

列名	字段类型	是否为空	含义
`emp_no`	int(11)	否	员工ID
`salary`	int(11)	否	薪水
`from_date`	date	否	开始日期
`to_date`	date	否	结束日期

`dept_emp` 部门员工表

该表存放部门与雇员的对应信息。使用emp_no与dept_no作为复合主键

列名	字段类型	是否为空	含义
`emp_no`	int(11)	否	员工ID
`dept_no`	char(4)	否	部门ID
`from_date`	date	否	开始日期
`to_date`	date	否	结束日期

`titles` 职称表

该表存放雇员与职称的对应信息。使用emp_no与from_date作为复合主键

列名	字段类型	是否为空	含义
`emp_no`	int(11)	否	员工ID
`title`	varchar(50)	否	职称
`from_date`	date	否	开始日期
`to_date`	date	否	结束日期

`emp_bonus` 奖金表

该表存放雇员与对应的奖金信息。

列名	字段类型	是否为空	含义
`emp_no`	int(11)	否	员工ID
`recevied`	datetime	否	发放时间
`btype`	smallint	否	奖金类型（1、2、3）

`departments` 部门表

该表存放部门的信息。

列名	字段类型	是否为空	含义
`dept_no`	char(4)	否	部门ID，主键
`dept_name`	varchar(40)	否	部门名称

四、题目

建议小伙伴们在做题的时候先把解题思路遮住，自己思考并尝试自己写，最后再与答案对照。另外很多题目解题方式不唯一，答案与解题思路仅供参考。

1 入职最晚的员工

题目描述：在员工表中查找最晚入职员工的所有信息

解题思路：

先找出所有入职时间里最大(最晚)的时间，在使用子查询的方式查找入职时间等于最晚时间的员工的所有信息，这样可以确保多条最晚入职的员工信息都可以被查询出来。

-- 第一种方式
SELECT * 
FROM employees
WHERE hire_date = (SELECT MAX(hire_date) 
                   FROM employees
                  );

-- 第二种方式
SELECT * 
FROM employees
WHERE hire_date = (SELECT hire_date 
                   FROM employees
                   ORDER BY hire_date DESC
                   LIMIT 1
                  );

注意：很多小伙伴可能会想到先对hire_date进行排序，然后使用LIMIT=1限制条数，但是使用ORDER BY按由大到小排序后选取第一条的方法仅限于当最晚入职数据只有一条时才正确，不能保证有多条同一最晚时间时的准确性。

SELECT * 
FROM employees
ORDER BY hire_date DESC
LIMIT 1;

2 入职时间排名倒数第三的员工

题目描述：在员工表中查找入职员工时间排名倒数第三的员工所有信息

解题思路：

同样使用子查询方法找到排名倒数第三的hire_date。在子查询中关键是要用DISTINCT去重，去重后相同入职日期只会保留一个，此时使用ORDER BY进行排序，然后使用LIMIT选择排名倒数第三的hire_date。

SELECT * 
FROM employees
WHERE hire_date = (
                    SELECT DISTINCT hire_date 
                    FROM employees
                    ORDER BY hire_date DESC 
                    LIMIT 2,1
                  );

3 领导的薪水

题目描述： 查找各个部门当前领导(to_date=’9999-01-01’)当前薪水详情以及其对应部门编号dept_no

解题思路：

通过dept_manager的emp_no=salaries的emp_no为联结条件联结两表，同时限制两表的to_date = ‘9999-01-01’筛选数据

SELECT s.*,d.dept_no
FROM salaries s 
JOIN dept_manager d ON s.emp_no = d.emp_no
WHERE d.to_date = '9999-01-01' 
  AND s.to_date = '9999-01-01';

4 所有员工的姓名与部门

题目描述：查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

解题思路：

使用employees作为主表，使用左联结，比较简单。

SELECT e.last_name,e.first_name,d.dept_no
FROM employees e 
LEFT JOIN dept_emp d ON e.emp_no = d.emp_no;

5 已经分配部门的员工

题目描述：查找所有已经分配部门的员工的last_name和first_name

解题思路：

这里考察LEFT JOIN的原理。使用employees作为主表左联结dept_emp，题目要查找所有已经分配部门的员工，意味着联结后必须要带有dept_no，没有dept_no的就是还未分配部门的，因此加上筛选条件dept_no为空即可求得。

SELECT e.last_name,e.first_name
FROM employees e 
LEFT JOIN dept_emp d ON e.emp_no = d.emp_no
WHERE d.dept_no is not null;

6 员工入职时的薪水

题目描述：查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_no进行逆序

解题思路：

使用左联结(内联结)，此题最关键的是salaries表的from_date 和 employees表的hire_date 的值应该要相等，因此有限制条件e.hire_date = s.from_date，两个date都是刚入职的时间。

再强调一下，salaries表中有多个相同emp_no的salary，即emp_no在salaries表中不唯一，因为同一员工在不同时间的工资不同，即涨薪或降薪。

SELECT e.emp_no,s.salary
FROM salaries s 
LEFT JOIN employees e ON s.emp_no = e.emp_no
WHERE e.hire_date = s.from_date
ORDER BY s.emp_no DESC;

7 员工当前薪水

题目描述：找出所有员工当前(to_date=’9999-01-01’)具体的薪水salary情况，最后只显示salary这一列，对于相同的薪水只显示一次,并按照逆序显示

解题思路：

关键理解对于相同的薪水只显示一次，使用分组方法对salary进行分组求得，也可以使用DISTINCT去重的方法，但是一般数据较多时使用DISTINCT效率相比GROUP BY 要慢，所以从性能角度考虑，应该尽量使用GROUP BY。

-- 第一种方式 使用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;

8 领导当前薪水

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

解题思路：

注意理解题目，键点是有两个“当前”。因此dept_manager表的to_date和salaries表的to_date都要等于’9999-01-01’，s.to_date = '9999-01-01'容易理解，限定当前时间当前工资，d.to_date = '9999-01-01'则限定在职经理。

SELECT d.dept_no,d.emp_no,s.salary
FROM salaries s LEFT JOIN dept_manager d ON d.emp_no = s.emp_no
WHERE d.to_date = '9999-01-01' AND s.to_date = '9999-01-01';

9 不是领导的员工

题目描述：获取所有非manager员工的emp_no

解题思路：

使用employees作为主表，使用左联结，限定条件为d.dept_no为空，选出在employees中但不在dept_manager中的emp_no记录。

SELECT e.emp_no
FROM employees e 
LEFT JOIN dept_manager d ON e.emp_no = d.emp_no
WHERE d.dept_no is null;

10 员工当前的领导

题目描述：获取所有员工的当前的emp_no与对应经理的manager_no，如果当前的manager是自己的话结果不显示，当前表示to_date='9999-01-01'。

解题思路：

重点要理解两表之间的逻辑关系和题目要求。有三个关键点：

每个部门都有一个manager，每个人都有自己所在的部门。所以用部门号dept_no作为联结条件，左联结和内联结都可以；
如果员工当前的manager是自己的话结果不显示，这就要求dept_emp表的员工编号emp_no和dept_manager的员工编号不相等；
题目要求当前的manager，所以最后的经理表dept_manager的时间为to_date=’9999-01-01’，严谨起见，员工表dept_emp的时间也应该是to_date=’9999-01-01’。

SELECT
  de.emp_no,
  dm.emp_no AS manager_no 
FROM
  dept_emp de
LEFT JOIN dept_manager dm ON de.dept_no = dm.dept_no 
WHERE
  de.to_date = '9999-01-01'
AND dm.to_date = '9999-01-01'
AND de.emp_no <> dm.emp_no

11 部门最高的员工薪水

题目描述：获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary

解题思路：

STEP1：先用内联结或左联结连接两张表，限制条件是两张表的emp_no相同，即d.emp_no = s.emp_no

STEP2：选取每个员工当前的工资水平，用d.to_date = '9999-01-01' AND s.to_date = '9999-01-01'作条件限制；

STEP3：用GROUP BY将每个部门分为一组，用MAX()函数选取每组中工资最高者；

STEP4：将salaries用s代替，dept_emp用d代替，最后将MAX(s.salary)用salary代替后输出。

SELECT
  d.dept_no,
  s.emp_no,
  MAX(s.salary) AS salary 
FROM salaries s
LEFT JOIN dept_emp d ON s.emp_no = d.emp_no 
WHERE s.to_date = '9999-01-01'
  AND d.to_date = '9999-01-01'
GROUP BY d.dept_no;

扩展： 如果某部门薪水最大的员工有多人怎么办？

STEP1：利用子查询首先创建两张表，一张为MAXsalary，用于存放当前每个部门薪水的最大值；另一张为currentsalary，用于存放当前每个部门所有员工的编号和薪水；

STEP2：限定条件为两张表的 dept_no 和 salary 相等，这样就可以找出当前每个部门所有薪水等于最大值的员工的相关信息；

STEP3：以下代码虽然稍长，仔细一看都是基于上面的基础解法变化而来的，中心思想就是绕开 MAX() 的特性限制，运用比较的方法选出多个相同的最大值。

SELECT currentsalary.dept_no,
       currentsalary.emp_no,
       currentsalary.salary AS salary
FROM 
-- 创建MAXsalary表用于存放当前每个部门薪水的最大值
    (
    SELECT d.dept_no, MAX(s.salary) AS salary
    FROM salaries AS s 
    INNER JOIN dept_emp As d
    ON d.emp_no = s.emp_no 
    WHERE d.to_date = '9999-01-01' 
      AND s.to_date = '9999-01-01'
    GROUP BY d.dept_no
    ) AS MAXsalary, 
-- 创建currentsalary表用于存放当前每个部门所有员工的编号和薪水
    (
    SELECT d.dept_no, s.emp_no, s.salary 
    FROM salaries AS s INNER JOIN dept_emp As d
    ON d.emp_no = s.emp_no 
    WHERE d.to_date = '9999-01-01' 
      AND s.to_date = '9999-01-01'
    ) AS currentsalary
-- 限定条件为两表的dept_no和salary均相等
WHERE currentsalary.dept_no = MAXsalary.dept_no
  AND currentsalary.salary = MAXsalary.salary

12 职称的数目

题目描述：从titles表获取按照title进行分组，找到每组个数大于等于2的title，给出title以及对应的数目t

解题思路：

使用GROUP BY将title分组，再用HAVING子句找出大于等于2的组，HAVING必须在GROUP BY之后，并且有GROUP BY才能使用HAVING，聚合参数也可以是COUNT(*) 和COUNT(emp_no) ，还要注意 >= 号之间不能有空格，否则会出错。

SELECT title,COUNT(*) 
FROM titles 
GROUP BY title 
HAVING COUNT(*) >= 2

拓展： 员工在不同阶段，可能担任了相同的职务，因此在每个title下，emp_no重复的员工需要忽略不计。要对这种情况如何查询？

解题思路：

先用GROUP BY将表格以title分组，再用COUNT(DISTINCT emp_no)可以统计同一title值且不包含重复emp_no值的记录条数

SELECT title, COUNT(DISTINCT emp_no) AS t 
FROM titles
GROUP BY title 
HAVING t >= 2

13 特定的员工信息

题目描述：查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

解题思路：

员工号为奇数，则emp_no取余应为1，last_name不为Mary，用!=表示，另外注意字符串要用单括号括起来，逆序用DESC。

SELECT * 
FROM employees 
WHERE emp_no % 2 = 1
  AND last_name != 'Mary'
ORDER BY hire_date DESC;

14 职称的平均工资

题目描述：统计出当前各个title类型对应的员工当前(to_date=’9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资。

解题思路：

使用GROUP BY 对title进行分组，当前用两表to_date=’9999-01-01’进行限定，平均工资用聚合函数avg。

SELECT t.title,avg(salary) AS avg
FROM salaries s
INNER JOIN titles t ON s.emp_no = t.emp_no 
WHERE s.to_date = '9999-01-01'
  AND t.to_date = '9999-01-01'
GROUP BY t.title;

15 薪水第二多的员工信息

题目描述：获取当前(to_date=’9999-01-01’)薪水第二多的员工的emp_no以及其对应的薪水salary

解题思路：

STEP1：先在工资表里找出最多工资，再以工资小于最大工资为限定条件，即salary < (SELECT MAX(salary)可以得到排名第二的工资。

STEP2：再使用MAX(salary)在除去排名第一工资的表里找最大的工资，即找到排名第二的工资。

STEP3：使用这种这个查询方法，不管工资第一多的员工有多少个都能查到工资第二多的员工，且当工资第二多的员工有多个时也都能查询出来。

SELECT emp_no, salary 
FROM salaries 
WHERE salary = 
    (
    SELECT MAX(salary) 
    FROM salaries 
    WHERE to_date = '9999-01-01' 
        AND salary < 
            (
            SELECT MAX(salary) 
            FROM salaries 
            WHERE to_date = '9999-01-01' 
            ) 
    );

更简单一点的写法，使用DISTINCT和LIMIT

SELECT emp_no,salary 
FROM salaries
WHERE to_date = '9999-01-01'  
  AND salary = (
                  SELECT DISTINCT salary 
                FROM salaries 
                  WHERE to_date = '9999-01-01' 
                ORDER BY salary DESC 
                LIMIT 1,1
                  )

16 薪水第二多的员工信息(禁用排序)

题目描述：查找当前薪水(to_date=’9999-01-01’)排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不准使用ORDER BY

解题思路：

本题只需要在上一题的基础上增加一个内联结(左联结)即可将薪水salary、last_name以及first_name查询出来。

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 
WHERE
  salary = 
  (
    SELECT MAX(salary) 
    FROM salaries 
    WHERE to_date = '9999-01-01' 
        AND salary < 
            (
            SELECT MAX(salary) 
            FROM salaries 
            WHERE to_date = '9999-01-01'
            ) 
    );

拓展：如果将第二高改为第三高，第四高呢？使用MAX函数嵌套的方式过于低效，能否写一种通用的方式来获取第N高的员工信息？

解题思路：薪水第二多的意思就是只有一个人比他多，这样只要用salary表自连接，再分组，分组条件为s1.salary < s2.salary。组内条数等于1的那条记录，就正好是第二多。如果要找第三多，那就让HAVING COUNT(*)=1就可以了如果有两个人比那个人多，这两个人的薪水值相同的话，还要用一下DISTINCT关键字去重。

SELECT e.emp_no,s.salary,e.last_name,e.first_name
FROM employees e
JOIN salaries s ON e.emp_no=s.emp_no 
    AND s.to_date='9999-01-01'
    AND s.salary = 
        (
         SELECT s1.salary
         FROM salaries s1
         JOIN salaries s2 ON s1.salary < s2.salary 
         WHERE s1.to_date='9999-01-01' 
           AND s2.to_date='9999-01-01'
         GROUP BY s1.salary
         HAVING COUNT(DISTINCT s2.salary) = 1-- 这里写第几高
         )

17 员工对应的部门

题目描述：查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

解题思路：

本题关键在于理清三表之间关联关系。需要使用两次左联结。:

STEP1：题目要求包括暂时没有分配部门的员工，所以将employees作为主表，使用左联结将其和dept_emp表联结，联结条件为员工编号emp_no，得出所有员工所在部门，并将结果命名为a。

STEP2：将查询结果a与departments左联结，联结条件为部门编号dept_no，求出部门名字dept_name。

SELECT last_name, first_name, dept_name 
FROM
  (
    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 
  ) a
LEFT JOIN departments dp ON a.dept_no = dp.dept_no;

18 薪水涨幅

题目描述：查找员工编号emp_no为10001其自入职以来的薪水salary涨幅值growth

解题思路：

对“入职以来的薪水salary涨幅值growth”有两种理解：

员工入职以来工资最大值减去工资最小值。
最新一次工资减去入职时的工资，即为涨幅值growth，此种方式更严谨。

-- 方法一
SELECT MAX(salary) - min(salary) AS growth 
FROM salaries 
WHERE emp_no = '10001';
-- 方法二
SELECT
(
 SELECT salary 
 FROM salaries 
 WHERE emp_no = '10001' 
 ORDER BY to_date DESC 
 LIMIT 1 
) 
- 
( 
 SELECT salary 
 FROM salaries 
 WHERE emp_no = '10001' 
 ORDER BY to_date ASC 
 LIMIT 1 
) AS growth;

FROM salaries;

19 所有员工的薪水涨幅

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

解题思路：

STEP1：先创建两个子查询，分别找出所有员工当前的薪水(子查询a)和所有员工入职时的薪水(如子查询b)

STEP2： a，b子查询运用的方法都是一样的，联结employees表和salaries表，左联结内联结都可以，子查询a限定条件直接为当前时间’9999-01-01’，子查询b限定条件为入职时间，每个员工入职时间不一样，所以要使用谓词 in 找出在工资表里所有员工对应的最小时间。最后a，b子查询都要对员工编号进行分组。

STEP3：最后将这两个子查询联结，联结条件为a.emp_no = b.emp_no，再按照题目要求对薪水涨幅进行升序排序。

SELECT
  a.emp_no,
  (a.salary - b.salary) AS growth
FROM
  (
    SELECT e.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'
    GROUP BY s.emp_no
  ) a,
  (
    SELECT e.emp_no,s.salary
    FROM employees e
    LEFT JOIN salaries s ON e.emp_no = s.emp_no
    WHERE s.to_date IN 
    (
        SELECT min(to_date)
        FROM salaries s
        GROUP BY s.emp_no
    )
    GROUP BY s.emp_no
  ) b
WHERE a.emp_no = b.emp_no
ORDER BY growth ASC;

20 各部门的工资记录

题目描述：统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及次数SUM

解题思路：

将departments和dept_emp联结(左联结内联结都可以)，得到员工对应的部门编号和部门名称后，再联结salaries表，通过对部门分组，得到每组工资数。

SELECT a.dept_no, a.dept_name, COUNT(*) AS SUM
FROM
  (
    SELECT d.dept_no,de.emp_no,d.dept_name
    FROM dept_emp de
    LEFT JOIN departments d ON de.dept_no = d.dept_no
  ) a
INNER JOIN salaries s ON a.emp_no = s.emp_no
GROUP BY a.dept_no;

21 工资排名

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

解题思路：

经典的排名问题。解决方式分为两种，一种使用开窗函数，另一种不使用。

开窗函数版本：

使用窗口函数DENSE_RANK() OVER()函数即可将实现薪水按照salary进行按照1-N的排名，相同salary并列。这里需要注意的是开窗函数在各种SQL查询引擎中都有广泛的使用，但是MySQL8.0以上的版本才支持开窗函数

窗口函数：<窗口函数> OVER ([PARITITION BY <列清单>] ORDER BY <排序用列清单>) [PARITITION BY <列清单>]可以省略。

在开窗函数中，有3个与排序相关的函数，分别问RANK()、DENSE_RANK()、ROW_NUMBER()：

RANK() OVER (ORDER BY <列名>)：计算排序，如果存在相同位次的记录，则会跳过之后的位次。如1,1,1,4
DENSE_RANK () OVER (ORDER BY <列名>)：计算排序，即使存在相同位次的记录，也不会跳过之后的位次。如1,1,1,2
ROW_NUMBER () OVER (ORDER BY <列名>)：计算排序，赋予唯一的连续位次。如：1,2,3,4

SELECT emp_no,salary,
       dense_rank() over (ORDER BY salary DESC) AS 'rank'
FROM salaries
WHERE to_date = '9999-01-01'
ORDER BY salary DESC,emp_no ASC;

普通版本：

SETP1：利用自联结，复用两张salaries进行比较排名，联结两表条件为to_date = ‘9999-01-01’。

SETP2：此外重点在于限定条件s1.salary <= s2.salary，在输出s1.salary的情况下，有多少个s2.salary大于等于s1.salary，即计数COUNT(DISTINCT s2.salary)，并以计数结果作为排序结果。

SETP3：由于两表联结，同样 salary 算作一样的排名，所以需要去重处理COUNT(DISTINCT s2.salary)。

SETP4：因为使用了合计函数，COUNT只返回一个值，所以必须进行分组查询，最后使用ORDER BY排序。

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

22 普通员工的薪水

题目描述：获取所有非manager员工当前的薪水情况，给出dept_no、emp_no以及salary ，当前表示to_date=’9999-01-01’

解题思路：

先联结员工表和工资表找出所有人的员工编号和工资，再联结部门员工表找出所有员工的编号，最后使用not in找出非manager 的员工。最后记得限定工资表salaries和员工表dept_emp的时间都为to_date=’9999-01-01’。

SELECT de.dept_no,e.emp_no,s.salary
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
AND s.to_date = '9999-01-01'
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE de.to_date = '9999-01-01'
  AND de.emp_no NOT IN (
                        SELECT emp_no FROM dept_manager
                        );

23 比领导薪水还高的员工

题目描述：获取员工其当前的薪水比其manager当前薪水还高的相关信息，当前表示to_date=’9999-01-01’，查询出员工的emp_no，其manager的manager_no，该员工当前的薪水emp_salary，该员工对应的manager当前的薪水manager_salary

解题思路：

将题目分解为三部分，做好链接即可：

显示员工以及其Manager
显示员工的薪水以及Manager的薪水
筛选员工薪酬比Manager高的部分人员，包括经理自己

SELECT de.emp_no,dm.emp_no manager_no,
      sa.salary emp_salary,sal.salary manager_salary
FROM dept_emp de,salaries sa,dept_manager dm,salaries sal
WHERE de.emp_no=sa.emp_no
  AND dm.emp_no=sal.emp_no
  AND de.dept_no=dm.dept_no
  AND de.to_date='9999-01-01'
  AND sa.to_date='9999-01-01'
  AND dm.to_date='9999-01-01'
  AND sal.to_date='9999-01-01'
  AND sa.salary>sal.salary

24 各部门的职称数量

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

解题思路：

重点理解各个部门下各个title类型的汇总。

先以员工编号为联结条件联结titles表和dept_emp表，得到员工部门编号和员工对应的title类型，再以部门编号为联结条件联结departments表，得到部门名称，最后记得限定titles表和dept_emp表的时间为当前to_date =’9999-01-01’。汇总的是各个部门下各个title类型，所以要先将部门编号进行一次汇总，再将tltle类型进行汇总，聚合函数COUNT的参数为title。

SELECT de.dept_no,dp.dept_name,
       t.title,COUNT(t.title) AS COUNT
FROM titles t
LEFT JOIN dept_emp de ON t.emp_no = de.emp_no
  AND t.to_date = '9999-01-01'
  AND de.to_date = '9999-01-01'
INNER JOIN departments dp ON de.dept_no = dp.dept_no
GROUP BY de.dept_no, t.title

25 查看姓名

题目描述：将employees表中的所有员工的last_name和first_name通过(-)连接起来。

解题思路：

考察字符串拼接函数CONCAT(<列1>,<列2>…)，多个列或字符串之间用个逗号隔开，这里last_name和first_name通过(-)连接，符号-要用双引号括起来。

SELECT CONCAT(last_name,"-",first_name) AS name
FROM employees;

26 查看字符出现的次数

题目描述：查找字符串'10,A,B' 中逗号','出现的次数cnt

解题思路：

STEP1：巧用length函数和replace，length函数计算字符串的长度，length("10,A,B")算出整个字符串的长度。

STEP2：使用replace将 ,逗号替换为空，那么整个字符串减少的长度等于逗号的长度，两者相减就是 , 出现的次数。

SELECT (length("10,A,B") - length(replace("10,A,B", ",", ""))) as cnt;

27 平均工资(排除最大与最小值)

题目描述：查找排除当前最大、最小salary之后的员工的平均工资

解题思路：

用两个子查询找出最大工资和最小工资，再用not in去除最大工资和最小工资，最后记得限定时间为当前。

SELECT AVG(salary) AS avg_salary
FROM salaries
WHERE salary NOT IN
    (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND salary NOT IN
    (SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01')
AND to_date = '9999-01-01';

28 员工的奖金

题目描述：获取有奖金的员工相关信息，查询出emp_no、first_name、last_name、奖金类型btype、对应的当前薪水情况salary以及奖金金额bonus。

bonus类型btype为1则其奖金为薪水salary的10%，btype为2其奖金为薪水的20%，其他类型均为薪水的30%。当前薪水表示*to_date=’9999-01-01’ *

解题思路：

STEP1：使用CASE WHEN表达式对奖金进行行列转换，这里使用的是CASE WHEN <列名> then …的搜索case表达式，最后要记得使用end结束case，并将结果重命名为BONUS。

STEP2：使用员工编号为限定条件将三表联结查询，还有限定时间为当前。

SELECT eb.emp_no,e.first_name,
       e.last_name,eb.btype,s.salary,
       (CASE WHEN eb.btype = 1 then s.salary * 0.1
             when eb.btype = 2 then s.salary * 0.2
             else s.salary * 0.3 end
       ) as bonus

FROM salaries s,emp_bonus eb,employees e
WHERE eb.emp_no = s.emp_no
 AND eb.emp_no = e.emp_no
 AND s.to_date = '9999-01-01';

29 薪水的累计

题目描述：按照salary的累计和running_total，其中running_total为前两个员工的salary累计和，其他以此类推。具体结果如下表展示。

emp_no	salary	running_total
10001	88958	88958
10002	72527	161485
10003	43311	204796
10004	74057	278853
10005	94692	373545
10006	43311	416856
10007	88070	504926
10009	95409	600335
10010	94409	694744
10011	25828	720572

窗口函数版本解题思路：

STEP1：本题关键在于把SUM聚合函数作为窗口函数使用，所有聚合函数都能用做窗口函数，其语法和专用窗口函数完全相同。

SUM(<汇总列>) over(<排序列>) as 别名；

STEP2：光看题目“前两个员工的salary累计和”不是很好理解，结合输出格式可以理解为running_total列是逐个员工的工资的累计和，每一行的工资都是前面所有行的工资总计。

STEP3：这有一个小bug，题目没有限定时间为当前，而按照输出格式来看和通过情况来看，只有限定时间为当前’9999-01-01’才能符合输出格式，才能通过，一开始考虑用员工分组，但是员工分组得到的结果并非题目本意，必须限定时间为当前。

SELECT emp_no,salary,
sum(salary) over(ORDER BY emp_no) as running_total
FROM salaries
WHERE to_date= '9999-01-01';

普通查询版本解题思路：

利用复表查询，以及running_total等于逐个员工的工资的累计和，即找出在表2中小于等于表1员工编号的所有员工工资进行求和，最后记得限定时间为当前，且要按照emp_no升序排序。

SELECT s1.emp_no, s1.salary,
(SELECT sum(s2.salary) 
 FROM salaries s2
 WHERE s2.emp_no <= s1.emp_no 
   AND s2.to_date = '9999-01-01'
) as running_total
FROM salaries s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.emp_no;

30 奇数行的员工姓名

题目描述：对于employees表中，给出奇数行的first_name

解题思路：

STEP1：窗口函数row_number的作用是赋予唯一的连续位次。巧用窗口函数row_number对数据进行行排序，对first_name进行排序，将得到的位次命名为row_num。

STEP2：用求余函数找出奇数行。

SELECT a.first_name
FROM (SELECT emp_no, first_name, 
             row_number() over(ORDER BY first_name) as row_num
      FROM employees) a
WHERE row_num % 2 = 1
ORDER BY emp_no;

五、进阶题目

本节题目只提供表结构且有一定难度,其中部分题目与四中类似，做不出来的小伙伴也不要灰心呦。可以先写下自己的思考过程再对照答案，会更有收获。

1、连续出现的数字(查连续出现N次的数据)

1.1、题目描述

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

例如，给定上面的 Logs 表， 1是唯一连续出现至少三次的数字。

ConsecutiveNums
1

1.2、解题方法

连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs并检查是否有 3 个连续的相同数字。

SELECT *
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;

Id	Num	Id	Num	Id	Num
1	1	2	1	3	1

注意：前两列来自 l1 ，接下来两列来自 l2 ，最后两列来自 l3 。

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num

思考一下，如果是将连续重复的数字的整条记录查询出来应该怎么做呢？

2、部门工资前三高的所有员工(分组求前N个)

2.1、题目描述

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name，工资 Salary 和部门编号 DepartmentId 。

Id	Name	Salary	DepartmentId
1	Joe	85000	1
2	Henry	80000	2
3	Sam	60000	2
4	Max	90000	1
5	Janet	69000	1
6	Randy	85000	1
7	Will	70000	1

Department 表包含公司所有部门的信息。

Id	Name
1	IT
2	Sales

编写一个 SQL 查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

Department	Employee	Salary
IT	Max	90000
IT	Randy	85000
IT	Joe	85000
IT	Will	70000
Sales	Henry	80000
Sales	Sam	60000

解释：

IT 部门中，Max 获得了最高的工资，RANDy 和 Joe 都拿到了第二高的工资，Will 的工资排第三。销售部门(Sales)只有两名员工，Henry 的工资最高，Sam 的工资排第二。

2.2、解题方法

1、我们先找出公司里前 3 高的薪水，意思是不超过三个值比这些值大

SELECT e1.Salary 
FROM Employee AS e1
WHERE 3 > 
        (SELECT COUNT(DISTINCT e2.Salary) 
         FROM    Employee AS e2 
          WHERE    e1.Salary < e2.Salary     AND e1.DepartmentId = e2.DepartmentId) ;

2、再把表 Department 和表 Employee 连接，获得各个部门工资前三高的员工。

举例说明：
当 e1 = e2 = [50,100,150,200,250]

e1.Salary = 50，e2.Salary 可以取值 [100,150,200,250]，COUNT(DISTINCT e2.Salary) = 4

e1.Salary = 100，e2.Salary 可以取值 [150,200,250]，COUNT(DISTINCT e2.Salary) = 3

e1.Salary = 150，e2.Salary 可以取值 [200,250]，COUNT(DISTINCT e2.Salary) = 2

e1.Salary = 200，e2.Salary 可以取值 [250]，COUNT(DISTINCT e2.Salary) = 1

e1.Salary = 250，e2.Salary 可以取值 []，COUNT(DISTINCT e2.Salary) = 0

最后 3 > COUNT(DISTINCT e2.Salary)，所以 e1.Salary 可取值为 [150,200,250]，即集合前 3 高的薪水

再把表 Department 和表 Employee 连接，获得各个部门工资前三高的员工。

SELECT
    Department.NAME AS Department,
    e1.NAME AS Employee,
    e1.Salary AS Salary 
FROM
    Employee AS e1,Department 
WHERE
    e1.DepartmentId = Department.Id 
    AND 3 > (SELECT  COUNT( DISTINCT e2.Salary ) 
             FROM    Employee AS e2 
             WHERE    e1.Salary < e2.Salary     AND e1.DepartmentId = e2.DepartmentId     ) 
ORDER BY Department.NAME,Salary DESC;

3、汇总成绩

3.1、题目描述

已知score表结构为下方所示:

name	subject	score
张三	语文	80
李四	语文	90
张三	数学	80
李四	数学	70
张三	英语	90
李四	英语	50

要求查询结果为：

name	chinese	math	english	score
张三	80	80	90	250
李四	90	70	50	210

3.2、解题方法

SUM-CASE方法

SELECT name
    ,SUM(CASE subject WHEN '语文' THEN score ELSE 0 END) AS chinese
    ,SUM(CASE subject WHEN '数学' THEN score ELSE 0 end) as math
    ,SUM(CASE subject WHEN '英语' THEN score ELSE 0 end) as english
    ,SUM(socre)as score
  FROM score GROUP BY name

IF方法

SELECT name ,
    sum(if(subject='语文',score,0)) as chinese,
    sum(if(subject='数学',score,0))as math, 
    sum(if(subject='英语',score,0))as english,
    SUM(score) as 总分
FROM score GROUP BY name

4、换座位

4.1、题目描述

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

假如数据输入的是上表，则输出结果如下：

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

题目来源：力扣(LeetCode)

4.2、解题方法

case关键字解法(最容易理解)

SELECT (CASE
WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id|1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id;

左连接

SELECT a.id,ifnull(b.student,a.student) as student 
FROM seat as a 
LEFT JOIN seat as b 
ON (a.id%2=1 && a.id=b.id-1) || (a.id%2=0 && a.id=b.id|1) 
ORDER BY a.id;

对id排序

SELECT if(id%2=0,id-1,if(id=cnt,id,id|1)) as id,student 
FROM (
       SELECT COUNT(*) as cnt FROM seat)as a,seat 
ORDER BY id;

异或

0^1=1 , 1^1=0 ,2^1=3 ,3^1=2

SELECT b.id,a.student 
FROM seat as a,seat as b,
(SELECT COUNT(*) as cnt FROM seat) as c 
WHERE b.id=1^(a.id-1)|1 || (c.cnt%2 && b.id=c.cnt && a.id=c.cnt);

5、体育馆的人流量(与第一题类似)

5.1、题目描述

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

例如，表 stadium：

id	visit_date	people
1	2017-01-01	10
2	2017-01-02	109
3	2017-01-03	150
4	2017-01-04	99
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

对于上面的示例数据，输出为：

id	visit_date	people
5	2017-01-05	145
6	2017-01-06	1455
7	2017-01-07	199
8	2017-01-08	188

提示：每天只有一行记录，日期随着 id 的增加而增加。

来源：力扣(LeetCode)

5.2、解题方法

与第一题相似，查找连续的三天，以下是最直观的思考：

SELECT a.*
FROM stadium as a,stadium as b,stadium as c
WHERE (a.id = b.id-1 AND b.id|1 = c.id) 
  AND (a.people>=100 AND b.people>=100 AND c.people>=100);

但是这样输出会有问题，比如 5,6,7,8 号人流量不少于100，但是只输出了 5,6号，根本原因在于，我们将 a 的 id 设为三个连续值中最小值，所以只返回了每 3 个连续值中最小的一个，同理可想到，我们再将 a 的 id 设为三个连续值中中间值和最大值，可以得到全部的连续 3 个值

SELECT a.*
FROM stadium as a,stadium as b,stadium as c
WHERE ((a.id = b.id-1 AND b.id|1 = c.id) or
       (a.id-1 = b.id AND a.id|1 = c.id) or
       (a.id-1 = c.id AND c.id-1 = b.id))
  AND (a.people>=100 AND b.people>=100 AND c.people>=100);

但是这样还有个问题，比如 5,6,7,8，6 既是 5,6,7 的中间值也是 6,7,8 的最小值，所以还要去重，也许 id 不按序排列，再排序 id，最终得到答案

SELECT DISTINCT a.*
FROM stadium as a,stadium as b,stadium as c
WHERE ((a.id = b.id-1 AND b.id|1 = c.id) or
       (a.id-1 = b.id AND a.id|1 = c.id) or
       (a.id-1 = c.id AND c.id-1 = b.id))
  AND (a.people>=100 AND b.people>=100 AND c.people>=100)
ORDER BY a.id;

6、第N高的薪水

6.1、题目描述

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水(Salary)。

Id	Salary
1	5000
2	7000
3	7000
4	9000
5	9000

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 7000。

getNthHighestSalary(2)
7000

6.2、解题方法

对薪水排序后直接查第N个

SELECT DISTINCT salary FROM Employee
WHERE  salary = (SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1,1)

依次将最高的薪水排除

-- 以N=2为例
SELECT MAX(salary) FROM Employee  
WHERE salary<>(SELECT MAX(salary) FROM Employee)

稍微难理解一点的写法

SELECT DISTINCT Salary FROM Employee e 
    WHERE N = (
        SELECT COUNT(DISTINCT Salary) 
            FROM Employee 
                WHERE Salary >= e.Salary )

解释：用表Employee的所有不重复的Salary值A 依次与该表的所有不重复的Salary值B 进行比对，找到符合A>=B条件的记录数量为N的A值。

7、交换性别(一条语句交换两个值)

7.1、题目描述

给定一个 salary 表，如下所示，有 m = 男性和 f = 女性的值。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求只使用一个更新(Update)语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

运行你所编写的更新语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

来源：力扣(LeetCode)

7.2、解题方法

CASE WHEN

UPDATE salary
SET
sex = CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END;

update salary set sex=if(sex = 'f', 'm','f');

ASCII码转换

update salary set sex = char(ascii('m') + ascii('f') - ascii(sex));

8、行程和用户

8.1、题目描述

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

Id	Client_Id	Driver_Id	City_Id	Status	Request_at
1	1	10	1	completed	2013-10-01
2	2	11	1	cancelled_by_driver	2013-10-01
3	3	12	6	completed	2013-10-01
4	4	13	6	cancelled_by_client	2013-10-01
5	1	10	1	completed	2013-10-02
6	2	11	6	completed	2013-10-02
7	3	12	6	completed	2013-10-02
8	2	12	12	completed	2013-10-03
9	3	10	12	completed	2013-10-03
10	4	13	12	cancelled_by_driver	2013-10-03

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。

Users_Id	Banned	Role
1	No	client
2	Yes	client
3	No	client
4	No	client
10	No	driver
11	No	driver
12	No	driver
13	No	driver

写一段 SQL 语句查出 2013年10月1日至 2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率(Cancellation Rate)保留两位小数。

取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

Day	Cancellation Rate
2013-10-01	0.33
2013-10-02	0.00
2013-10-03	0.50

来源：力扣(LeetCode)

8.2、解题方法

SELECT a.Request_at as Day,
round(sum(case status when 'completed' then 0 else 1 end)/COUNT(*),2) as 'CancellatiON Rate'  
FROM Trips a 
        LEFT JOIN Users b ON a.Client_Id  =b.Users_Id  
        LEFT JOIN Users c ON a.Driver_Id  =c.Users_Id  
            WHERE a.Request_at between '2013-10-01' AND '2013-10-03'
                AND b.Banned ='No' AND c.Banned ='No'
                    GROUP BY a.Request_at

9、上升的温度

9.1、题目描述

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。

Id(INT)	RecordDate(DATE)	Temperature(INT)
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-03	20
4	2015-01-04	30

例如，根据上述给定的 Weather 表格，返回如下 Id:

Id
2
4

来源：力扣(LeetCode)

9.2、解题方法

1、直接使用日期加减然后关联

SELECT w1.Id
FROM Weather w1 JOIN Weather w2
ON w1.RecordDate = w2.RecordDate+1
WHERE w1.Temperature > w2.Temperature

2、使用datediff函数

SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.date, w.date) = 1
AND weather.Temperature > w.Temperature

10、分数排名(经典的排名问题)

10.1、题目描述

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名(Rank)相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

例如，根据上述给定的 Scores 表，你的查询应该返回(按分数从高到低排列)：

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

来源：力扣(LeetCode)

10.2、解题方法

1、直接使用SQL书写

SELECT a.Score as Score,
(SELECT COUNT(DISTINCT b.Score) FROM Scores b WHERE b.Score >= a.Score) as Rank
FROM Scores a
ORDER BY a.Score DESC

2、使用开窗函数

Yuhao Wang

http://yuhaowang.top/2020/02/26/SQL%E9%A2%98%E7%9B%AE%E4%B8%8E%E8%A7%A3%E6%9E%90/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Yuhao Wang !

SQL

我的理财逻辑

聊聊自己的理财逻辑

2020-03-15 理财

理财

2020年理财记录

记录下自己2020年的理财历程

2020-02-14 理财

理财基金股票

SQL练习题与解析

一、练习说明

二、在线练习网站推荐

三、练习数据准备

数据导入

数据结构

employees 员工表

dept_manager 部门领导表

salaries 薪水表

dept_emp 部门员工表

titles 职称表

emp_bonus 奖金表

departments 部门表

四、题目

1 入职最晚的员工

2 入职时间排名倒数第三的员工

3 领导的薪水

4 所有员工的姓名与部门

5 已经分配部门的员工

6 员工入职时的薪水

7 员工当前薪水

8 领导当前薪水

9 不是领导的员工

10 员工当前的领导

11 部门最高的员工薪水

12 职称的数目

13 特定的员工信息

14 职称的平均工资

15 薪水第二多的员工信息

16 薪水第二多的员工信息(禁用排序)

17 员工对应的部门

18 薪水涨幅

19 所有员工的薪水涨幅

20 各部门的工资记录

21 工资排名

22 普通员工的薪水

23 比领导薪水还高的员工

24 各部门的职称数量

25 查看姓名

26 查看字符出现的次数

27 平均工资(排除最大与最小值)

28 员工的奖金

29 薪水的累计

30 奇数行的员工姓名

五、进阶题目

1、连续出现的数字(查连续出现N次的数据)

1.1、题目描述

1.2、解题方法

2、部门工资前三高的所有员工(分组求前N个)

2.1、题目描述

2.2、解题方法

3、汇总成绩

3.1、题目描述

3.2、解题方法

4、换座位

4.1、题目描述

4.2、解题方法

5、体育馆的人流量(与第一题类似)

5.1、题目描述

5.2、解题方法

6、第N高的薪水

6.1、题目描述

6.2、解题方法

7、交换性别(一条语句交换两个值)

7.1、题目描述

7.2、解题方法

8、行程和用户

8.1、题目描述

8.2、解题方法

9、上升的温度

9.1、题目描述

9.2、解题方法

10、分数排名(经典的排名问题)

10.1、题目描述

10.2、解题方法

你的赏识是我前进的动力

`employees` 员工表

`dept_manager` 部门领导表

`salaries` 薪水表

`dept_emp` 部门员工表

`titles` 职称表

`emp_bonus` 奖金表

`departments` 部门表